USEFUL MS SQL STATEMENT for Triggers and Constraints

------------

-- Disable all triggers and constraints in a database

------------

USE CopyOfAdventureWorks;

-- SQL disable all triggers

EXEC sp_MSforeachtable @command1="ALTER TABLE ? DISABLE TRIGGER ALL"

GO

-- SQL disable all constraints

EXEC sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL"

GO

-- Enable all triggers on a table

ALTER TABLE Production.Product ENABLE TRIGGER ALL

-- Enable all check contraints on a table

ALTER TABLE Production.Product CHECK CONSTRAINT ALL

GO

------------

-- Single constraint disable and enable

------------

USE CopyOfAdventureWorks;

-- SQL disable constraint

ALTER TABLE Production.Product NOCHECK CONSTRAINT CK_Product_DaysToManufacture

GO

-- SQL enable constraint

ALTER TABLE Production.Product CHECK CONSTRAINT CK_Product_DaysToManufacture

GO

-- SQL enable constraint with check of current data

ALTER TABLE Production.Product WITH CHECK

CHECK CONSTRAINT CK_Product_DaysToManufacture

GO

-- SQL enable constraint with no check of current data

ALTER TABLE Production.Product WITH NOCHECK

CHECK CONSTRAINT CK_Product_DaysToManufacture

-- Check integrity of all constraints on a table

DBCC CHECKCONSTRAINTS('Production.Product');

GO

/* DBCC execution completed. If DBCC printed error messages,

contact your system administrator.

*/

------------

------------

-- Single trigger disable and enable

------------

-- SQL disable trigger

ALTER TABLE Sales.SalesOrderHeader DISABLE TRIGGER uSalesOrderHeader

GO

-- SQL enable trigger

ALTER TABLE Sales.SalesOrderHeader ENABLE TRIGGER uSalesOrderHeader

GO

------------

------------

-- Create a check constraint for a table and enable it

------------

-- SQL create check constraint

-- Range constraint - column value must be between 0 and 100

USE AdventureWorks;

ALTER TABLE [Production].[ProductInventory] WITH CHECK

ADD CONSTRAINT [CK_ProductInventory_Bin]

CHECK (([Bin]>=(0) AND [Bin]<=(100)))

GO

-- SQL enable check constraint

ALTER TABLE [Production].[ProductInventory]

CHECK CONSTRAINT [CK_ProductInventory_Bin]

GO