How can I create a disabled trigger in SQL Server 2005?

Question

When using Oracle you can create a disabled trigger specifing the word DISABLE before the trigger body. How can I achive the same effect in Sql Server?

Answer 1

In management studio Expand Trigger folder under table and Right Click on Trigger and Disable.

DISABLE TRIGGER { [ schema_name . ] trigger_name [ ,...n ] | ALL }
ON { object_name | DATABASE | ALL SERVER } [ ; ]

MSDN:DISABLE TRIGGER (Transact-SQL)

ENABLE TRIGGER { [ schema_name . ] trigger_name [ ,...n ] | ALL }
ON { object_name | DATABASE | ALL SERVER } [ ; ]

MSDN:ENABLE TRIGGER (Transact-SQL)

sql-server-disable-all-triggers-on-a-database-disable-all-triggers-on-all-servers

Answer 2

T-SQL provides a DISABLE TRIGGER statement that accomplishes the same thing. You can find the details here: DISABLE TRIGGER SYNTAX

Answer 3

If you really must create the trigger disabled then create and disable it within a transaction:

begin tran
go
create trigger t_i on t after insert as begin /* trigger body */ end
go
disable trigger t_i on t
commit
go

The GOs are there because CREATE TRIGGER must be the first statement in a batch, but depending on how you deploy your code you can probably make it look a bit neater.