Disabling Triggers

To prevent triggers from running when data arrives through replication, you can add the NOT FOR REPLICATION option to the CREATE TRIGGER or ALTER TRIGGER statements. In this case, the trigger will fire on direct modifications to the base table, but not from subscription actions.

Temporarily, you can disable a trigger to speed up some processes. To do so, you can use the ALTER TABLE statement with the DISABLE TRIGGER option, as in Listing 9.18.

Code Listing 9.18. You Can Disable a Trigger
					
USE Northwind
GO

-- To disable a single trigger

ALTER TABLE Employees
DISABLE TRIGGER tr_Employees --, isr_Employees, udt_Employees

-- To disable several triggers from the same table
ALTER TABLE Employees
DISABLE TRIGGER tr_Employees, isr_Employees, udt_Employees

-- To disable all the triggers from a table

ALTER TABLE Employees
DISABLE TRIGGER ALL

To reenable the trigger, use the ALTER TABLE statement with the ENABLE TRIGGER option. Listing 9.19 shows how to reenable the triggers that were disabled in Listing 9.18.

Code Listing 9.19. You Can Reenable a Trigger
					
USE Northwind
GO

-- To enable a single trigger

ALTER TABLE Employees
ENABLE TRIGGER tr_Employees --, isr_Employees, udt_Employees

-- To enable several triggers from the same table

ALTER TABLE Employees
ENABLE TRIGGER tr_Employees, isr_Employees, udt_Employees

-- To enable all the triggers from a table

ALTER TABLE Employees
ENABLE TRIGGER ALL

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.22.27.45