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.
LVL 1
rot299Asked:
Who is Participating?
 
appariCommented:


try setting nested triggers off. but this option applies for whole database, so if you are not using nested triggers then you can safely set it to off.
how to set the option see this link from MSDN
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_config_4y3y.asp
0
 
rot299Author Commented:
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.
0
 
ChristianGantarCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.