I’m not sure if this is a new feature in sql 2005 or not but I don’t recall ever seeing it in SQL 2000. I saw it this morning in one of my co-workers .sql file. In the past, I had to drop the triggers and recreate them
DISABLE TRIGGER { [ schema . ] trigger_name [ ,...n ] | ALL }
ON { object_name | DATABASE | ALL SERVER } [ ; ]
Example from BOL:
Disabling a DML trigger on a table
The following example disables trigger Address that was created on table Address.
USE AdventureWorks;
GO
DISABLE TRIGGER Person.uAddress ON Person.Address;
GO
Disabling a DDL trigger
The following example creates a DDL trigger safety with database scope, and then disables it.The following example creates a DDL trigger safety with database scope, and then disables it.
IF EXISTS (SELECT * FROM sys.triggers
WHERE parent_class = 0 AND name = ‘safety’)
DROP TRIGGER safety ON DATABASE;
GO
CREATE TRIGGER safety
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
PRINT ‘You must disable Trigger “safety” to drop or alter tables!’
ROLLBACK;
GO
DISABLE TRIGGER safety ON DATABASE;
GO
Disabling all triggers that were defined with the same scope
The following example disables all DDL and logon triggers that were created at the server scope.
USE AdventureWorks;
GO
DISABLE Trigger ALL ON ALL SERVER;
GO
You can also enable the triggers. Look up ENABLE TRIGGER in BOL