Vô hiệu hóa các trigger và các constraints .

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. Forgetting to turn on disabled/dropped constraints/triggers is a frequent production problem because constraint & triggers "don't complain" when turned off/dropped as opposed to functions and stored procedures.
2. Check if a Foreign Key constraint enabled or disabled.
3. Create a trigger for insert, update & delete.
4. 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

-- How to check if a trigger is disabled

USE AdventureWorks2008;

SELECT * FROM sys.triggers WHERE name='uSalesOrderHeader'

/* name     object_id   parent_class      parent_class_desc parent_id   type  type_desc      create_date modify_date is_ms_shipped     is_disabled is_not_for_replication      is_instead_of_trigger

uSalesOrderHeader 1579152671  1     OBJECT_OR_COLUMN  1010102639  TR    SQL_TRIGGER 2010-05-09 06:17:20.033   2010-05-09 06:17:20.033 0     0     1     0

*/

-- 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

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

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

-- Check/test if a foreign key constraint is enabled or disabled

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

CREATE TABLE Author (

   AuthorID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,

   Name NVARCHAR(64) NOT NULL,

   ModifiedDate date default(getdate()) )

  

CREATE TABLE Book (

   BookID INT IDENTITY(1,1)  NOT NULL PRIMARY KEY,

   AuthorID INT NOT NULL,

  

   CONSTRAINT fkBookAuthor FOREIGN KEY (AuthorID)

                           REFERENCES Author (AuthorID) ON DELETE CASCADE,

   BookTitle NVARCHAR(128) NOT NULL,

   Price SMALLMONEY  NOT NULL,

   ModifiedDate date default(getdate()) )

GO

-- Disable FK constraint

ALTER TABLE Book NOCHECK CONSTRAINT fkBookAuthor

-- Command(s) completed successfully.

-- Check if constraint disabled

EXEC sp_help Book

/* constraint_type      constraint_name   delete_action     update_action     status_enabled      status_for_replication  constraint_keys

FOREIGN KEY fkBookAuthor      Cascade     No Action   Disabled    Is_For_Replication      AuthorID

*/

-- Enable constraint

ALTER TABLE Book CHECK CONSTRAINT fkBookAuthor

-- How to check if a constraint is enabled

EXEC sp_helpconstraint 'Book'

/* constraint_type      constraint_name   delete_action     update_action     status_enabled      status_for_replication  constraint_keys

FOREIGN KEY fkBookAuthor      Cascade     No Action   Enabled     Is_For_Replication      AuthorID

*/

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

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

-- 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]

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

Source : sqlusa