CODEWITHSUNDEEP
t-sqlstored-procedurestriggerssql-server-2008-r2
KentZhou
KentZhou

Reputation: 25573

how to stop/fire trigger in stored procedure dynamically?

Suppose I have a table MyTab in database. I have a trigger, for example, delete trigger on this table. Then in a stored procedure, I try to delete data from this table but want to stop the delete trigger only for this deletion. After that, put the trigger back on normal. Is it possible to have codes in stored procedure like:

stop trigger on MyTab;
delete from MyTab where ...;
put the trigger back;

Upvotes: 3

Views: 5161

Answers (1)

Tim Lehner
Tim Lehner

Reputation: 15251

As mentioned here, you can disable and enable the trigger, though I'd probably put this in a try-catch and/or transaction so you don't get stuck with your trigger disabled because of an error.

For example:

set xact_abort on; -- Auto-rollback on any error
begin transaction;

    alter table MyTab disable trigger TR_MyTab_Delete;

    delete from MyTab where 1/0 = 1; -- Causes div by zero error

    alter table MyTab enable trigger TR_MyTab_Delete; -- Won't run becuase of above error

commit;

The script above will throw an error but won't leave the trigger disabled, as set xact_abort on guarantees that my transaction is rolled back, including the disabling of the trigger.

Upvotes: 4

Related Questions