Disable and Create Triggers


————

— 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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: