Search code examples
sqlsql-serversql-server-2005triggersddl

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

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?

Solution

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