Execute the following
Microsoft T-SQL example scripts in Management Studio Query Editor to demonstrate the following:
1. Disable all triggers and constraints in the CopyOfAdventureWorks database.
Warning: data captured is not validated anymore. Prior to turning
them back on (enable), data has to be verified.
2. Create a trigger for insert, update & delete.
3. Create a check constraint for a table and enable it.
------------
-- Disable all triggers and constraints in a database
------------
-- Disable trigger stored procedure - disable constraint stored procedure
USE CopyOfAdventureWorks;
-- SQL disable all triggers - disable all triggers sql server - t sql disable trigger
EXEC sp_MSforeachtable @command1="ALTER TABLE ? DISABLE TRIGGER ALL"
GO
-- SQL disable all constraints - disable all constraints sql server
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
-- SQL enable all triggers - enable all triggers sql server - t sql enable trigger
EXEC sp_MSforeachtable @command1="ALTER TABLE ? ENABLE TRIGGER ALL"
GO
-- SQL enable all constraints - enable all constraints sql server
-- sp_MSforeachtable is an undocumented system stored procedure
EXEC sp_MSforeachtable @command1="ALTER TABLE ? CHECK CONSTRAINT ALL"
GO
------------
-- Single constraint disable and enable
------------
USE CopyOfAdventureWorks;
-- SQL disable constraint - alter table remove 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 disable trigger
ALTER TABLE Sales.SalesOrderHeader DISABLE TRIGGER uSalesOrderHeader
GO
-- SQL enable trigger
ALTER TABLE Sales.SalesOrderHeader ENABLE TRIGGER uSalesOrderHeader
GO
------------
------------
-- SQL Server 2008 T-SQL insert, update, delete trigger demo
------------
USE tempdb;
CREATE TABLE TriggerDemo (
ID int identity(1,1) Primary Key,
TextData varchar (64)
)
GO
IF OBJECT_ID ('dbo.PrintTrigger','TR') IS NOT NULL
DROP TRIGGER Sales.reminder2;
GO
CREATE TRIGGER PrintTrigger
ON TriggerDemo
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
DECLARE @InsertedMessage varchar(max) = 'New: '+(SELECT TextData FROM inserted)
DECLARE @DeletedMessage varchar(max) = 'Old: '+(SELECT TextData FROM deleted)
PRINT @InsertedMessage
PRINT @DeletedMessage
END
GO
INSERT TriggerDemo (TextData) VALUES ('Enable trigger demo')
GO
-- In messages: New: Enable trigger demo
UPDATE TriggerDemo SET TextData = 'Disable trigger demo'
GO
/*
New: Disable trigger demo
Old: Enable trigger demo
*/
DELETE TriggerDemo
GO
-- Old: Disable trigger demo
DROP TABLE TriggerDemo
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
|
