Disabling a trigger 2

[mtawk]

Hello,
I'm using a trigger in a table and i need to stop it for a certain treatment in my code and then reactivate it.
Is there a way to do so in SQL server ?
Thanks for any help

 

[mimi2]

what i would do is go to "manage triggers" for the particular table then comment out every thing that comes after the declaration of the trigger itself.

 

[MeanGreen]

In BOL you can enable and disable triggers.

Alter Table <tablename> disable Trigger <ALL or triggerName>

Hope this helps.

 

[nealvenables]

Alternatively, are you saying that there are certain cases where your code should be able to update a table without invoking that table's trigger?

If so, the following method is inelegant but works...

1. Add a bit column called &quot;SupressTrg&quot; (for instance) to the table involved.

2. When your code updates the table, make sure the update also sets a value for &quot;SupressTrg&quot; (true or false depending on whether or not the trigger should be supressed).

3. Structure your trigger along these lines

CREATE TRIGGER trgMyTrigger ON tblMyTable
FOR UPDATE
AS
-- Is trigger supressed?
IF EXISTS (SELECT SuppressTrig
FROM inserted
WHERE (SuppressTrig = 0))
BEGIN
-- Nope, so execute trigger code
-- (trigger code here)
END
-- Reset trigger supression flag
UPDATE tblMyTable
SET SupressTrig = 0
FROM tblMyTable, inserted
WHERE tblMyTable.PK = inserted.PK


Horrible, I know, but I have used this method successfully to get myself out of a corner I'd managed to paint myself into.