Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

Quickly Enabling and Disabling Constraints and Triggers

By Brian Knight,

In a previous article, I covered how to use the sp_msforeachtable system stored procedure to look through a list of tables and perform an action on them. You may want to read that article before continuing.

When loading data, nothing can be more frustrating than having to deal with stubborn constraints and triggers. This is especially true when you know your data is fine but the constraints are too prohibitive for a mass initial load. Here's a technique I use to disable the constraints and triggers temporarily during a mass load.

To do this, we can use the sp_msforeachtable system stored procedure to loop through each table and issue an ALTER TABLE statement to dsable the constraint and trigger. The ALL clause at the end of the command will disable all constraints and triggers. There is no need to drop referential integrity to load data. If you turn on NOCHECK, constraints will not be checked. Here's the code to perform this:

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

To enable them back, you can reverse the function. Let's get a little more creative this time. We'll explicitly set the parameters and list each table before enabling them so we can verify any errors easily.

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

As I mentioned before, you never want to drop constraints during development. Instead disable them temporarily until the problem is debugged.

Total article views: 9078 | Views in the last 30 days: 17
Related Articles

Disable and enable trigger

Disable and enable trigger


Check Constraint Person Relations

Triggers - Constraints


Disabling Trigger on Replicated Table

Way of disabling trigger on replicated table


Is CHECK constraint same as TRIGGER?

Is CHECK constraint = After TRIGGER?


Disable foreign constraint

Disable foreign constraint

advanced querying    
sql server 7    

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones