SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Quickly Enable/Disable Triggers and Constraints

By Brian Knight,

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.

Total article views: 2896 | Views in the last 30 days: 4
Related Articles

Is CHECK constraint same as TRIGGER?

Is CHECK constraint = After TRIGGER?


Check Constraint Person Relations

Triggers - Constraints


Quickly Enabling and Disabling Constraints and Triggers

In some cases, you may have to be able to quickly disable all the constraints and triggers in a data...



Constraint with a check


Create Delete trigger that has Foreign key constraints on the table

Create Delete trigger that has Foreign key constraints on the table