DISABLE TRIGGER (Transact-SQL)DISABLE TRIGGER (Transact-SQL)

适用于: 是SQL Server是Azure SQL 数据库否Azure SQL 数据仓库否并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

禁用触发器。Disables a trigger.

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

DISABLE TRIGGER { [ schema_name . ] trigger_name [ ,...n ] | ALL }  
ON { object_name | DATABASE | ALL SERVER } [ ; ]  

参数Arguments

schema_nameschema_name
触发器所属架构的名称。Is the name of the schema to which the trigger belongs. 不能为 DDL 或登录触发器指定 schema_name 。schema_name cannot be specified for DDL or logon triggers.

trigger_name trigger_name
要禁用的触发器的名称。Is the name of the trigger to be disabled.

ALLALL
指示禁用在 ON 子句作用域中定义的所有触发器。Indicates that all triggers defined at the scope of the ON clause are disabled.

注意

SQL ServerSQL Server 在为合并复制发布的数据库中创建触发器。creates triggers in databases that are published for merge replication. 在已发布数据库中指定 ALL 可禁用这些触发器,这样会中断复制。Specifying ALL in published databases disables these triggers, which disrupts replication. 在指定 ALL 之前,请验证没有为合并复制发布当前数据库。Verify that the current database is not published for merge replication before specifying ALL.

object_nameobject_name
对其创建了要执行的 DML 触发器 trigger_name 的表或视图的名称。Is the name of the table or view on which the DML trigger trigger_name was created to execute.

DATABASEDATABASE
对于 DDL 触发器,指示所创建或修改的 trigger_name 将在数据库作用域内执行。For a DDL trigger, indicates that trigger_name was created or modified to execute with database scope.

ALL SERVERALL SERVER
适用范围SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017.

对于 DDL 触发器,指示所创建或修改的 trigger_name 将在服务器作用域内执行。For a DDL trigger, indicates that trigger_name was created or modified to execute with server scope. ALL SERVER 也适用于登录触发器。ALL SERVER also applies to logon triggers.

备注

此选项在包含数据库中不可用。This option is not available in a contained database.

RemarksRemarks

默认情况下,创建触发器后会启用触发器。Triggers are enabled by default when they are created. 禁用触发器不会删除该触发器。Disabling a trigger does not drop it. 该触发器仍然作为对象存在于当前数据库中。The trigger still exists as an object in the current database. 但是,当执行编写触发器程序所用的任何 Transact-SQLTransact-SQL 语句时,不会激发触发器。However, the trigger does not fire when any Transact-SQLTransact-SQL statements on which it was programmed are executed. 可以使用 ENABLE TRIGGER 重新启用触发器。Triggers can be re-enabled by using ENABLE TRIGGER. 还可以通过使用 ALTER TABLE 来禁用或启用为表定义的 DML 触发器。DML triggers defined on tables can be also be disabled or enabled by using ALTER TABLE.

使用 ALTER TRIGGER 语句更改触发器将启用此触发器。Changing the trigger by using the ALTER TRIGGER statement enables the trigger.

权限Permissions

若要禁用 DML 触发器,用户必须至少对为其创建触发器的表或视图具有 ALTER 权限。To disable a DML trigger, at a minimum, a user must have ALTER permission on the table or view on which the trigger was created.

若要禁用具有服务器范围 (ON ALL SERVER) 的 DDL 触发器或登录触发器,用户必须对服务器拥有 CONTROL SERVER 权限。To disable a DDL trigger with server scope (ON ALL SERVER) or a logon trigger, a user must have CONTROL SERVER permission on the server. 若要禁用数据库范围 (ON DATABASE) 中的 DDL 触发器,用户必须至少对当前数据库具有 ALTER ANY DATABASE DDL TRIGGER 权限。To disable a DDL trigger with database scope (ON DATABASE), at a minimum, a user must have ALTER ANY DATABASE DDL TRIGGER permission in the current database.

示例Examples

AdventureWorks2012 数据库中介绍了以下示例。The following examples are described in the AdventureWorks2012 database.

A.A. 禁用对表的 DML 触发器Disabling a DML trigger on a table

以下示例禁用对表 uAddress 创建的触发器 AddressThe following example disables trigger uAddress that was created on table Address.

DISABLE TRIGGER Person.uAddress ON Person.Address;  
GO  

B.B. 禁用 DDL 触发器Disabling a DDL trigger

以下示例在数据库范围创建 DDL 触发器 safety,然后禁用该触发器。The following example creates a DDL trigger safety with database scope, and then disables it.

CREATE TRIGGER safety   
ON DATABASE   
FOR DROP_TABLE, ALTER_TABLE   
AS   
   PRINT 'You must disable Trigger "safety" to drop or alter tables!'   
   ROLLBACK;  
GO  
DISABLE TRIGGER safety ON DATABASE;  
GO  

C.C. 禁用以同一作用域定义的所有触发器Disabling all triggers that were defined with the same scope

下例禁用在服务器范围内创建的所有 DDL 触发器。The following example disables all DDL triggers that were created at the server scope.

DISABLE Trigger ALL ON ALL SERVER;  
GO  

另请参阅See Also

ENABLE TRIGGER (Transact-SQL) ENABLE TRIGGER (Transact-SQL)
ALTER TRIGGER (Transact-SQL) ALTER TRIGGER (Transact-SQL)
CREATE TRIGGER (Transact-SQL) CREATE TRIGGER (Transact-SQL)
DROP TRIGGER (Transact-SQL) DROP TRIGGER (Transact-SQL)
sys.triggers (Transact-SQL)sys.triggers (Transact-SQL)