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

When using Oracle you can create a disabled trigger specifing the word DISABL开发者_Go百科E before the trigger body. How can I achive the same effect in Sql Server?

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.

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.

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

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

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