ISTEAD OF INSERT trigger

First we create a table described bellow

CREATE TABLE t1

            (ids   varchar(1),

             sname varchar(50))

 

Now create a trigger on it (Normal Insert Trigger)

 CREATE TRIGGER t1_trig ON t1

 FOR INSERT

 AS

 INSERT INTO t1

 SELECT ids, sname FROM inserted

 

What happened when we insert a statement on table t1 like this

INSERT INTO t1

VALUES('1', 'ABC')

It affects 2 records,

Ids       sanme

1          ABC

1          ABC

 

Now replace the trigger with this

DROP TRIGGER t1_trig

 

CREATE TRIGGER t1_trig  on t1

INSTEAD OF INSERT

AS

BEGIN

  INSERT INTO t1

       SELECT ids, sname FROM inserted

END

 

Now insert the value

INSERT INTO t1

VALUES('1', 'DEF')

It affects 1 records,

Ids       sanme

1          DEF

 

Now got the differences? It works on SQL 2008 Only.

References : http://msdn.microsoft.com/en-us/library/ms175089.aspx

Posted by : MR. JOYDEEP DAS