ENABLE TRIGGER (Transact-SQL)ENABLE TRIGGER (Transact-SQL)

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

启用 DML、DDL 或登录触发器。Enables a DML, DDL, or logon trigger.

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

语法Syntax

ENABLE 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 can't be specified for DDL or logon triggers.

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

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

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

启用触发器并不是要重新创建它。Enabling a trigger doesn't re-create it. 禁用的触发器仍以对象形式存在于当前数据库中,但并不触发。A disabled trigger still exists as an object in the current database, but doesn't fire. 启用触发器将导致在运行触发器最初编程时所针对的任何 Transact-SQLTransact-SQL 语句时触发。To enable a trigger, causes it to fire when any Transact-SQLTransact-SQL statements on which it was originally programmed are run. 可以使用 DISABLE TRIGGER 禁用触发器。Triggers are disabled by using DISABLE TRIGGER. 此外,还可以通过使用 ALTER TABLE 来禁用或启用为表定义的 DML 触发器。DML triggers defined on tables can also be disabled or enabled by using ALTER TABLE.

权限Permissions

若要启用 DML 触发器,用户需要至少对于创建触发器所在的表或视图拥有 ALTER 权限。To enable a DML trigger, at a minimum, a user needs ALTER permission on the table or view on which the trigger was created.

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

示例Examples

A.A. 在表中启用 DML 触发器Enabling a DML trigger on a table

以下示例禁用在 AdventureWorks 数据库的表 Address 中创建的触发器 uAddress,然后再启用它。The following example disables trigger uAddress that was created on table Address in the AdventureWorks database, and then enables it.

DISABLE TRIGGER Person.uAddress ON Person.Address;  
GO  
ENABLE Trigger Person.uAddress ON Person.Address;  
GO  

B.B. 启用 DDL 触发器Enabling a DDL trigger

以下示例会创建具有数据库作用域的 DDL 触发器 safety,并且先禁用然后再启用该触发器。The following example creates a DDL trigger safety with database scope, and then disables and enables 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  
ENABLE TRIGGER safety ON DATABASE;  
GO  

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

以下示例启用在服务器作用域级别创建的所有 DDL 触发器。The following example enables all DDL triggers that were created at the server scope.

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

ENABLE Trigger ALL ON ALL SERVER;  
GO  

另请参阅See Also

DISABLE TRIGGER (Transact-SQL) DISABLE 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)