————

— 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

• Always qualify objects by owner
• Do not use * in SELECT statements, always specify columns
• Query “with (nolock)” when you don’t require high transactional consistency
• Do not use GOTO
• Avoid cursor use. If necessary always declare the correct type of cursor (FAST_FORWARD)
• Avoid SELECT INTO for populating temp tables. Create the table then use INSERT SELECT.
• Always use ANSI join syntax
• Always check for object existance
• Use SCOPE_IDENTITY() instead of @@IDENTITY
• Always check @@TRANCOUNT and commit/rollback as necessary
• Order DML to avoid deadlocks
• Always check @@ERROR and @@ROWCOUNT by assigning to a variable
• Always check sp return values
• Do not create cross database dependencies
• Avoid table value UDF – performance problems
• Avoid dynamic SQL – if necessary use sp_executesql over EXEC
• Avoid using NULL values
• When there are only two values, ISNULL is more efficient than COALESCE
• Try to avoid “SELECT *”, except these two cases: “WHERE EXISTS (SELECT * …)” and “SELECT COUNT(*) …”