Databases & DBA

How To Disable All Triggers on a Database


To enable & disable all the triggers and constraints we have a system defined Stored Procedure which are as under:

1) Disable all the triggers for a single database:

USE AdventureWorks;
GO
DISABLE TRIGGER Person.uAddress ON AdventureWorks;
GO

2) Disable all the triggers for all servers:

USE AdventureWorks;
GO
DISABLE TRIGGER ALL ON ALL SERVER;
GO

3) Disabling all Constraints
exec sp_MSforeachtable ALTER TABLE ? NOCHECK CONSTRAINT ALL
 
4) Disabling all Triggers
exec sp_MSforeachtable ALTER TABLE ? DISABLE TRIGGER ALL

5) Enabling all Constraints
exec sp_MSforeachtable ALTER TABLE ? CHECK CONSTRAINT ALL

6) Enabling all Triggers
exec sp_MSforeachtable ALTER TABLE ? ENABLE TRIGGER ALL

For more detail plz read: http://msdn.microsoft.com/en-us/library/ms189748.aspx
Ebook Download
View all
Learn
View all
Membership not found