CODEWITHSUNDEEP
sqlsql-serversql-server-2005triggersddl
regisbsb
regisbsb

Reputation: 3814

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?

Upvotes: 7

Views: 5260

Answers (6)

Pacho
Pacho

Reputation: 56

1st create empty trigger (performing no action), 2nd disable it, 3rd alter it with the desired content, 4th enable it when needed

Upvotes: 0

Alin Hanghiuc
Alin Hanghiuc

Reputation: 31

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

Upvotes: 1

user1575457
user1575457

Reputation: 21

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.

Upvotes: 2

Pondlife
Pondlife

Reputation: 16240

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.

Upvotes: 18

Kashif
Kashif

Reputation: 14430

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

Upvotes: -1

Vincent Ramdhanie
Vincent Ramdhanie

Reputation: 103135

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

Upvotes: -1

Related Questions