Disable a trigger temporarily.

In my project I was thinking is it possible to disable all triggers temporarily?

Then answer is yes. We can disable it. The alternate is we can drop them and re-create it but it's not good way.

Any how to disable trigger it's not good idea because it can create a lot more issue with the data integrity. But you can perform this action when you alone are working on a database or in test environment or bulk inserting data.

But make sure once you have done your work enable all the trigger so you can maintain data integrity.

While you are importing bulk data then trigger should not fire once it's done then you have to fix to fire it again when actual data is coming.

Whenever I am doing this thing then I am disabling all the trigger using following command.

To disable all constraints and trigger:

sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER all"

To enable all constraints and trigger:

exec sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? ENABLE TRIGGER

What Next

• Stumble it
• Digg this
• Save to Delicious
• Tweet it
• Subscribe

Related Posts