Hii All,

I just find a problem with instead of trigger. I wanted to fire insteadof Insert trigger from Insteadof Update trigger code. But I found that Insteadof Insert trigger did not fire from Update trigger insert code. Below i am give the problem description.

I created two Instead of triggers in my database TestDB.

1. First trigger is for update on table T1, inside which i write code to print a message that 'INSTEADOFUPDATE' trigger Fired' and also code to Insert statement on Table T1.
2. Second trigger is for Insert, inside which i write code to print a message that 'INSTEADOFINSERT' trigger Fired'.

Following is the code that i run.

IF DB_ID('TestDB') IS NOT NULL 
    DROP DATABASE TestDB
GO
CREATE DATABASE TestDB
GO
USE TestDB;
CREATE TABLE T1 (name Varchar(50) NULL)
GO
INSERT INTO T1(name) VALUES('ASHISH')
GO

CREATE TRIGGER TR_T1_IST_UPD
ON T1
INSTEAD OF UPDATE
AS
BEGIN
    PRINT 'INSTEAD_OF_UPDATE TRIGGER FIRED.'
    INSERT INTO T1(name) VALUES('PRASHANT')
END
GO

CREATE TRIGGER TR_T1_IST_INS
ON T1
INSTEAD OF INSERT
AS
BEGIN
    PRINT 'INSTEAD_OF_INSERT TRIGGER FIRED.'
END
GO
Select * from T1 

UPDATE T1 SET name='ASHISH JAIN' WHERE name='ASHISH'  

INSTEAD_OF_UPDATE TRIGGER FIRED.
(1 row(s) affected)
(1 row(s) affected)

Now when i query table T1. I have two rows having values 'ASHISH' and 'PRASHANT'. It means that instead of Insert trigger does not get fired and insert statement executed successfully:

 Select * from T1; 

 
name
--------------------------------------------------
ASHISH
PRASHANT

When i Altered TRT1ISTINS trigger from INSTEAD OF trigger to After trigger on same table. and run the same update statement then both triggers TRT1ISTUPD and TRT1IST_INS got fired. following is the code for this change.

 
ALTER TRIGGER TR_T1_IST_INS
ON T1
AFTER INSERT
AS
BEGIN
PRINT 'INSTEAD_OF_INSERT TRIGGER FIRED.'
END

GO

UPDATE T1 SET name='ASHISH JAIN' WHERE name='ASHISH'

INSTEAD_OF_UPDATE TRIGGER FIRED.
INSTEAD_OF_INSERT TRIGGER FIRED.

(1 row(s) affected)
(1 row(s) affected)
 

 Select * from T1; 

 
name
--------------------------------------------------
ASHISH
PRASHANT
PRASHANT