Solved

how to make before insert trigger in sql server 2005?

Posted on 2011-03-16
16
969 Views
Last Modified: 2012-05-11
hi,

I want to create new trigger that should be triggered before inserting the record, the trigger is going to convert XYZ field nvarchar string from inserting record to lower chars and check it across to lower XYZ field in table

if there is match, the record must not be inserted, otherwise the record can be inserted
0
Comment
Question by:njgroup
  • 7
  • 5
  • 3
  • +1
16 Comments
 
LVL 4

Expert Comment

by:qasim_md
ID: 35146688
I hope this helps:::

The Create Before Insert Trigger in SQL fires the trigger before we insert the value into the table.

Understand with Example

The given Tutorial describe you a code on 'Create Before Insert Trigger in SQL'. To grasp this example, we create a table 'Stu_Table'. The createtable is used to create a table 'Stu_Table' with field attribute and data type respectively.

Create Table Stu_Table

Create Table Stu_Table
(Stu_Id int,Stu_Name Varchar(15),Sub1 int,Sub2 int,Sub3 int,
Sub4 int,Sub5 int,total int,per float,status varchar(15));

Create Trigger Stu_Insert

Now, we create a Trigger 'Stu_Insert' on table stu_table. The Before Insert ON  trigger is fired first before adding a records or rows to the table 'Stu_Table'.  

delimiter $$

CREATE TRIGGER stu_insert

Before Insert ON stu_table FOR EACH ROW
BEGIN

      set new.total = new.sub1 + new.sub2 +new.sub3 +
                  new.sub4 +new.sub5;
      set new.per = new.total/5;

      if new.per<33 then
            set new.status="fail";

      elseif new.per>=33 and new.per<45 then
            set new.status="3rd Div";

      elseif new.per>=45 and new.per<60 then
            set new.status="2nd  Div";

      else
            set new.status="1st Div";
      end if;
END$$
delimiter ;

Insert Data Into Stu_Table

insert into stu_table( Stu_Id, Stu_Name, sub1, sub2, sub3, sub4, sub5)
                   values (1, 'AAA', 6, 6, 6, 6, 6);

insert into stu_table( Stu_Id, Stu_Name, sub1, sub2, sub3, sub4, sub5)
                   values (2, 'BBB', 33, 33, 33, 33, 40);

insert into stu_table( Stu_Id, Stu_Name, sub1, sub2, sub3, sub4, sub5)
                   values (2, 'CCC', 45, 45, 45, 45, 50);

insert into stu_table( Stu_Id, Stu_Name, sub1, sub2, sub3, sub4, sub5)
                   values (2, 'DDD', 67, 67, 67, 67, 67);
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35146745
>if there is match, the record must not be inserted, otherwise the record can be inserted

you could implement that part using a unique constraint, refusing the insert.
otherwise, you needed a INSTEAD OF trigger, that could silently "drop" the INSERT ...

in regards to the trigger syntax itself, have you already tried something?
0
 

Author Comment

by:njgroup
ID: 35147067
qasim_md, is that oracle or ms sql?

I dont see before as keyword

I got error on it
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35147263
that was MySQL syntax, which won't work on MS SQL server
0
 

Author Comment

by:njgroup
ID: 35147404
so any expert can help me in MS SQL 2005

I dont want mysql or oracle or any other,

I need it in ms sql 2005,

I have a column "EnProductType" in database, I just want to compare the inserted value if it is similar or like (%) all values of EnProductType of the table then the row must not be inserted.

I mean

IF select count(*) from myrable where EnProductType like %selected_EnProductType %
Then DONT INSERT THE ROW
ELSE  INSERT THE ROW


how to do that in a trigger in sql server 2005?
0
 

Author Comment

by:njgroup
ID: 35147407
IF select count(*) from myrable where EnProductType like %selected_EnProductType % > 0
Then DONT INSERT THE ROW
ELSE  INSERT THE ROW
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35147924
I repeat: what about using a unique constraint on the table?
if will raise an error to any application trying to run a INSERT that would result in a duplicate, you don't need a trigger for that.
if the collation of the column is not case senstive, you don't actually need to convert to lowercase to compare (and the unique constraint would not need either)

in other words: are you 200% sure you need a trigger?
a trigger is slower than such a constraint.
0
 

Author Comment

by:njgroup
ID: 35154208
the unique constraint is not enough, because I want to use like % string....

for example if I got this value in EnProductType column: "IP Camera"
I want it if someone try to insert record to "EnProductType" with value "ip camera sony" to decline that insert so the trigger must not insert insert this record in the table.

yes I am sure I need it in a trigger
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 4

Expert Comment

by:samijsr
ID: 35154373
MS SQL Server Create Dynamic Table Named Inserted while Insert and Deleted while Delete and for Update both Table will be reated

You can reference these Table in Trigger ti fire

Create Trigger Tr1
on Table1 for Insert
As
Declare @nRow int
Set @nRow=(Select count(*) from table1 inner join Inserted on lower(Table1.xyz)=lower(inserted.xyz))
if @nRow>=1
Begin
 Roll back Tran
end
else
 update Table1 Set XYZ =lower(XYZ)
end
0
 

Author Comment

by:njgroup
ID: 35154663
I did one, but its not working for instead of insert:

but its not working:

 
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER TRIGGER [tgr_ProductType_Instead_Of_Insert] ON [dbo].[ProductType]
INSTEAD OF INSERT
AS

	DECLARE @enProductType varchar(50) 
	DECLARE @arProductType nvarchar(50) 
	DECLARE @enDescription varchar(MAX) 
	DECLARE @arDescription nvarchar(MAX)

	DECLARE @countRecordMatch    int

	select @enProductType = (SELECT dbo.TRIM(EnProductType) FROM inserted)
	select @arProductType = (SELECT dbo.TRIM(ArProductType) FROM inserted)
	select @enDescription = (SELECT CAST(EnDescription AS NVARCHAR(MAX)) FROM inserted)
	select @arDescription = (SELECT CAST(ArDescription AS NVARCHAR(Max)) FROM inserted)

	select @countRecordMatch = (select count(*) from ProductType where UPPER(dbo.TRIM(EnProductType)) = UPPER(@enProductType))

	IF @countRecordMatch = 0
	BEGIN
		insert into ProductType (EnProductType, ArProductType, EnDescription, ArDescription) values (@enProductType, @arProductType, @enDescription, @arDescription)
	END

Open in new window

0
 
LVL 4

Accepted Solution

by:
samijsr earned 300 total points
ID: 35155080
for use the Like Key word Modify the Trigger

Create Trigger Tr1
on Table1 for Insert
As
Declare @nRow int
Declare @xyz nvarchar(50)
Set @xyz=(Select xyz from Inserted)
Set @xyz=lowe(@xyz)+'%'
Set @nRow=(Select count(*) from table1 where xyz like @xyz)
if @nRow>=1
Begin
 Roll back Tran
end
else
 update Table1 Set XYZ =lower(XYZ)
end
0
 

Author Comment

by:njgroup
ID: 35156151
but the problem I dont want to insert then roll back!

I wanna do before insert (instead of insert)
0
 
LVL 4

Expert Comment

by:samijsr
ID: 35156352
You can use Instead of Insert in Place of for Insert and change the If condition

if @nRow=0
Begin
   Insert into Table1
   Select * from Inserted
end
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35156625
:)

which is what I suggested in my first comment already :)
...otherwise, you needed a INSTEAD OF trigger, that could silently "drop" the INSERT ...
0
 

Author Comment

by:njgroup
ID: 35157087
but there is problem here in:

   Insert into Table1
   Select * from Inserted

because the id of the table is auto increment

here is the error:

Msg 8101, Level 16, State 1, Procedure tgr_ProductType_Instead_Of_Insert, Line 16
An explicit value for the identity column in table 'ProductType' can only be specified when a column list is used and IDENTITY_INSERT is ON.



0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 200 total points
ID: 35157302
you will have to list all the fields without the ID field in both destination table and source table.

so, you list all the columns except ID column:
Insert into Table1 (col2, col3 ... )
   Select col2, col3 ... 
     from Inserted

Open in new window

0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now