SQL Server Performance Forum – Threads Archive

Logistics question.

I have created a stored procedure which deletes from about 15 tables (some of which have triggers). I do not wish for these triggers to fire so I am disabling them. My question is should I disable all the triggers at the beginning of the sp and enable them at the end? Or should I wrap each delete with the alter table statements? -chili P.S. Could you provide a short technical explanation of your choice.

---

Disabling at the beginning and enabling at the end is ideal as far as I’m concerned.
First advantage is you control all in one place.
Can involve only those triggers which can be fired during this delete process.
_________
Satya SKJ
Moderator
SQL-Server-Performance.Com

---

I guess the second way is right. Wrapping each delete with it’s alter table statement. Because, if you disable triggers on all 15 tables and while you are deleting from one table, what if someone tries to delete from any of the other 14 tables and trigger on that table supposed to fire ?

---

Thanks for responses guys. Does anyone care to break the tie? -chili

---

I guess it depends on:
– who you expect to run this procedure, only sa, dbo or the owner of the table can disable/enable triggers
– whether you need concurrent access. disabling at the start and enabling at the end inside a single transaction would give you the easiest solution but not very concurrent
Cheers
Twan

---

If you don’t care about data integrity and if you are the only one doing this deletes, it would matter from the point of view performance whether you disable all the triggers at the beginning or each alongside its parent table (Assuming all the work is being done within one batch). Your choice will then be reduced to a matter of preference, rather than be based on any technical facts. Nathan H.O.
Moderator
SQL-Server-Performance.com

---

]]>