如何在SQLServer2005中创建禁用的触发器?

如果确实必须创建触发器disabled,则在事务中创建并禁用它:

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

之所以有GO,是因为CREATE TRIGGER必须是批处理中的第一条语句,但根据部署代码的方式,可能会使代码看起来更整洁一些。

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

EXEC('DISABLE TRIGGER trigger_on_myTable ON myTable');

这允许我在同一个脚本中创建和禁用,而不需要任何GO。

DISABLE TRIGGER SYNTAX

在ManagementStudio中,展开表下的触发器文件夹,右键单击触发器并禁用。

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

如果您更喜欢不需要事务的解决方案,但这也消除了触发器在创建和禁用触发器之间触发和执行操作的机会,那么您可以创建触发器,基本上不包含任何代码,然后禁用它,然后将其改为包含其实际主体:

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