• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 585
  • Last Modified:

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.
0
rot299
Asked:
rot299
1 Solution
 
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now