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

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.

Answer 2

The way I did it was to EXEC both the create and the disable like:

EXEC('CREATE TRIGGER trigger_on_myTable ON myTable <Trigger body> ');

EXEC('DISABLE TRIGGER trigger_on_myTable ON myTable');

This allowed me to create and disable in the same script without the any GO's.

Answer 3

If you prefer a solution that doesn't require a transaction, but that also eliminates the chance of the trigger firing and doing its thing between the moment it is created and the moment it is disabled, then you can create the trigger with basically no code in it, then disable it, and then alter it to include its actual body:

create trigger dbo.MyTrigger
    on dbo.MyTable
    after insert

as

declare @Foo int;

--Trigger body must have at least a statement (or an "external name") so that's why the above dummy declare.

go

disable trigger dbo.MyTrigger
    on dbo.MyTable;

go

alter trigger dbo.MyTrigger
    on dbo.MyTable
    after insert

as

declare @Foo int;

--Remove above declare statement and insert actual trigger code here.

go