February 2, 2012 at 2:58 am
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.
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.
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 Now I have one row in Table T1 having name 'ASHISH'. I want to fire TR_T1_IST_INS Trigger from TR_T1_IST_UPD trigger. For that I run an Update statement on Table T1, so that TR_T1_IST_UPD will fire and Instead of Update trigger code will fire and because i have Insert statement in TR_T1_IST_UPD trigger, This insert statement will fire TR_T1_IST_INS trigger, so Instead of insert TR_T1_IST_INS trigger code should run. Below is the code that i run to do this test and the output i got in result.
UPDATE T1 SET name='ASHISH JAIN' WHERE name='ASHISH'
Output :
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;
Output :
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'
OUTPUT :
INSTEAD_OF_UPDATE TRIGGER FIRED.
INSTEAD_OF_INSERT TRIGGER FIRED.
(1 row(s) affected)
(1 row(s) affected)
Query the table T1 I got three rows.
Select * from T1;
Output :
name
--------------------------------------------------
ASHISH
PRASHANT
PRASHANT
February 2, 2012 at 5:05 am
This was removed by the editor as SPAM
February 2, 2012 at 9:34 pm
I am not using recursive trigger its nested, one update statement fires update instedof trigger and under update instedof trigger there is insert statement that should fire another trigger( insert insteadof trigger) but it is not firing.... Why is it so??Plzz Help
February 3, 2012 at 3:30 am
This was removed by the editor as SPAM
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply