CODEWITHSUNDEEP
sql-servert-sqltriggersddldatabase-trigger
Brendan Reynolds
Brendan Reynolds

Reputation: 1021

Will it help if I check if a SQL Server trigger is enabled before I try to disable it?

I'm trouble-shooting some performance problems in a SQL Server database, and one of the things I found is that the stored procedures frequently disable and re-enable triggers. I'd love to have the authority and the time to redesign the thing so that it doesn't do this, but I don't.

As things stand at the moment, it's entirely possible for one stored procedure to disable a trigger, and then for another stored procedure to execute and want to disable the same trigger. Am I right in thinking that when this happens, the second stored procedure will have to wait for the first stored procedure to re-enable the trigger and release it's Sch-M lock on the table, so that the second stored procedure can acquire it's own lock and disable the trigger that the first stored procedure just re-enabled? If so, would it help at all if I modified the stored procedures to check if the trigger is already disabled before attempting to disable it?

Upvotes: 0

Views: 589

Answers (1)

allmhuran
allmhuran

Reputation: 4454

Am I right in thinking that when this happens, the second stored procedure will have to wait for the first stored procedure to re-enable the trigger and release

Yes, as long as the first procedure is holding a transaction open.

If so, would it help at all if I modified the stored procedures to check if the trigger is already disabled before attempting to disable it?

You can do this by checking is_disabled on sys.triggers, but when doing so you would have to read with (nolock) (read uncommitted isolation level).

Nick is quite right, you have wandered into a minefield here. Note that you won't be able to modify data in the table while the first procedure runs anyway

Upvotes: 1

Related Questions