asked on

Disable update trigger being called via insert or delete trigger

Greetings,

I have a table that currently contains an insert and delete trigger and now I want to add an update trigger to the same table. The problem is that both the insert and delete triggers contain update statements and they will cause an update trigger to be fire and I do not want this to occur.

Is there a way I can disable the update trigger only if it was called via the insert / delete triggers?

I'm using MSSQL 2000.

ASKER CERTIFIED SOLUTION

appari

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rot299

ASKER

Thanks I feel stupid now hehe, I knew about nested triggers but just assumed it was turned off. Turns out it was enabled as I was doing some testing on our dev server a little while back and forgot to turn it off.

Thanks anyway.

ChristianGantar

I suggest to use the context info of ms sql as follows:

USE test
GO

CREATE TABLE TestTbl
(
recid INTEGER NOT NULL IDENTITY PRIMARY KEY,
wert VARCHAR(9) NOT NULL,
datum DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
)
GO
INSERT INTO dbo.TestTbl (wert) VALUES ('Eintrag 1')
INSERT INTO dbo.TestTbl (wert) VALUES ('Eintrag 2')
INSERT INTO dbo.TestTbl (wert) VALUES ('Eintrag 3')
GO

alter TRIGGER trTestTblInsert ON dbo.TestTbl FOR INSERT
AS
begin
IF (@@ROWCOUNT = 0)
RETURN
BEGIN
set context_info 0x1;
UPDATE
dbo.TestTbl
SET
datum = '19990101'
WHERE recid IN (SELECT recid FROM INSERTED)
END
set context_info 0x0;
end
GO

alter TRIGGER trTestTblUpdate ON dbo.TestTbl FOR UPDATE
AS
begin
declare @Context_INfo varbinary(128)
select @Context_INfo = Context_INfo from master.dbo.sysprocesses where spid=@@SPID;
IF (@@ROWCOUNT = 0 or (@Context_INfo = 0x1) )
RETURN
BEGIN
UPDATE
dbo.TestTbl
SET
datum = '19281231'
WHERE recid IN (SELECT recid FROM INSERTED)
END
end
GO

christian.gantar@aon.at