DROP TRIGGER (Transact-SQL)DROP 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 触发器。Removes one or more DML or DDL triggers from the current database.

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

语法Syntax

-- Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)  
  
DROP TRIGGER [ IF EXISTS ] [schema_name.]trigger_name [ ,...n ] [ ; ]  
  
-- Trigger on a CREATE, ALTER, DROP, GRANT, DENY, REVOKE or UPDATE statement (DDL Trigger)  
  
DROP TRIGGER [ IF EXISTS ] trigger_name [ ,...n ]   
ON { DATABASE | ALL SERVER }   
[ ; ]  
  
-- Trigger on a LOGON event (Logon Trigger)  
  
DROP TRIGGER [ IF EXISTS ] trigger_name [ ,...n ]   
ON ALL SERVER  

参数Arguments

IF EXISTS IF EXISTS
适用范围SQL ServerSQL ServerSQL Server 2016 (13.x)SQL Server 2016 (13.x)当前版本SQL 数据库SQL Database)。Applies to: SQL ServerSQL Server ( SQL Server 2016 (13.x)SQL Server 2016 (13.x) through current version, SQL 数据库SQL Database).

有条件地删除触发器(仅当其已存在时)。Conditionally drops the trigger only if it already exists.

schema_nameschema_name
DML 触发器所属架构的名称。Is the name of the schema to which a DML trigger belongs. DML 触发器的作用域是为其创建该触发器的表或视图的架构。DML triggers are scoped to the schema of the table or view on which they are created. 不能为 DDL 或登录触发器指定 schema_name 。schema_name cannot be specified for DDL or logon triggers.

trigger_name trigger_name
要删除的触发器的名称。Is the name of the trigger to remove. 要查看当前已创建触发器的列表,请使用 sys.server_assembly_modulessys.server_triggersTo see a list of currently created triggers, use sys.server_assembly_modules or sys.server_triggers.

DATABASEDATABASE
指示 DDL 触发器的作用域应用于当前数据库。Indicates the scope of the DDL trigger applies to the current database. 如果在创建或修改触发器时也指定了 DATABASE,则必须指定 DATABASE。DATABASE must be specified if it was also specified when the trigger was created or modified.

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 触发器的作用域应用于当前服务器。Indicates the scope of the DDL trigger applies to the current server. 如果在创建或修改触发器时也指定了 ALL SERVER,则必须指定 ALL SERVER。ALL SERVER must be specified if it was also specified when the trigger was created or modified. ALL SERVER 也适用于登录触发器。ALL SERVER also applies to logon triggers.

备注

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

RemarksRemarks

可以通过删除 DML 触发器或删除触发器表来删除 DML 触发器。You can remove a DML trigger by dropping it or by dropping the trigger table. 删除表时,将同时删除与表关联的所有触发器。When a table is dropped, all associated triggers are also dropped.

删除触发器时,会从 sys.objects、sys.triggers 和 sys.sql_modules 目录视图中删除有关该触发器的信息。 When a trigger is dropped, information about the trigger is removed from the sys.objects, sys.triggers and sys.sql_modules catalog views.

仅当所有触发器均使用相同的 ON 子句创建时,才能使用一个 DROP TRIGGER 语句删除多个 DDL 触发器。Multiple DDL triggers can be dropped per DROP TRIGGER statement only if all triggers were created using identical ON clauses.

若要重命名触发器,可使用 DROP TRIGGER 和 CREATE TRIGGER。To rename a trigger, use DROP TRIGGER and CREATE TRIGGER. 若要更改触发器的定义,可使用 ALTER TRIGGER。To change the definition of a trigger, use ALTER TRIGGER.

有关确定特定触发器依赖关系的详细信息,请参阅 sys.sql_expression_dependenciessys.dm_sql_referenced_entities (Transact-SQL)sys.dm_sql_referencing_entities (Transact-SQL)For more information about determining dependencies for a specific trigger, see sys.sql_expression_dependencies, sys.dm_sql_referenced_entities (Transact-SQL), and sys.dm_sql_referencing_entities (Transact-SQL).

有关查看触发器文本的详细信息,请参阅 sp_helptext (Transact-SQL)sys.sql_modules (Transact-SQL)For more information about viewing the text of the trigger, see sp_helptext (Transact-SQL) and sys.sql_modules (Transact-SQL).

有关查看现有触发器列表的详细信息,请参阅 sys.triggers (Transact-SQL)sys.server_triggers (Transact-SQL)For more information about viewing a list of existing triggers, see sys.triggers (Transact-SQL) and sys.server_triggers (Transact-SQL).

权限Permissions

要删除 DML 触发器,需要具有对于定义该触发器所在的表或视图的 ALTER 权限。To drop a DML trigger requires ALTER permission on the table or view on which the trigger is defined.

若要删除定义了服务器范围 (ON ALL SERVER) 的 DDL 触发器或删除登录触发器,需要对服务器拥有 CONTROL SERVER 权限。To drop a DDL trigger defined with server scope (ON ALL SERVER) or a logon trigger requires CONTROL SERVER permission in the server. 若要删除定义了数据库范围 (ON DATABASE) 的 DDL 触发器,要求在当前数据库中具有 ALTER ANY DATABASE DDL TRIGGER 权限。To drop a DDL trigger defined with database scope (ON DATABASE) requires ALTER ANY DATABASE DDL TRIGGER permission in the current database.

示例Examples

A.A. 删除 DML 触发器Dropping a DML trigger

以下示例将删除 AdventureWorks2012AdventureWorks2012 数据库中的 employee_insupd 触发器。The following example drops the employee_insupd trigger in the AdventureWorks2012AdventureWorks2012 database. (从 SQL Server 2016 (13.x)SQL Server 2016 (13.x) 开始,可使用 DROP TRIGGER IF EXISTS 语句。)(Beginning with SQL Server 2016 (13.x)SQL Server 2016 (13.x) you can use the DROP TRIGGER IF EXISTS syntax.)

IF OBJECT_ID ('employee_insupd', 'TR') IS NOT NULL  
   DROP TRIGGER employee_insupd;  

B.B. 删除 DDL 触发器Dropping a DDL trigger

以下示例将删除 DDL 触发器 safetyThe following example drops DDL trigger safety.

重要

因为 DDL 触发器不在架构范围内,所以不会在 sys.objects 目录视图中出现,无法使用 OBJECT_ID 函数来查询数据库中是否存在 DDL 触发器。 Because DDL triggers are not schema-scoped and, therefore do not appear in the sys.objects catalog view, the OBJECT_ID function cannot be used to query whether they exist in the database. 必须使用相应的目录视图来查询架构范围以外的对象。Objects that are not schema-scoped must be queried by using the appropriate catalog view. 对于 DDL 触发器,可使用 sys.triggers。 For DDL triggers, use sys.triggers.

DROP TRIGGER safety  
ON DATABASE;  

另请参阅See Also

ALTER TRIGGER (Transact-SQL) ALTER TRIGGER (Transact-SQL)
CREATE TRIGGER (Transact-SQL) CREATE TRIGGER (Transact-SQL)
ENABLE TRIGGER (Transact-SQL) ENABLE TRIGGER (Transact-SQL)
DISABLE TRIGGER (Transact-SQL) DISABLE TRIGGER (Transact-SQL)
EVENTDATA (Transact-SQL) EVENTDATA (Transact-SQL)
获取有关 DML 触发器的信息 Get Information About DML Triggers
sp_help (Transact-SQL) sp_help (Transact-SQL)
sp_helptrigger (Transact-SQL) sp_helptrigger (Transact-SQL)
sys.triggers (Transact-SQL) sys.triggers (Transact-SQL)
sys.trigger_events (Transact-SQL) sys.trigger_events (Transact-SQL)
sys.sql_modules (Transact-SQL) sys.sql_modules (Transact-SQL)
sys.assembly_modules (Transact-SQL) sys.assembly_modules (Transact-SQL)
sys.server_triggers (Transact-SQL) sys.server_triggers (Transact-SQL)
sys.server_trigger_events (Transact-SQL) sys.server_trigger_events (Transact-SQL)
sys.server_sql_modules (Transact-SQL) sys.server_sql_modules (Transact-SQL)
sys.server_assembly_modules (Transact-SQL)sys.server_assembly_modules (Transact-SQL)