DROP TRIGGER (Transact-SQL)DROP TRIGGER (Transact-SQL)

適用於: 是SQL Server 是Azure SQL Database 否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 EXISTSIF EXISTS
適用於SQL ServerSQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x)目前的版本SQL DatabaseSQL Database)。Applies to: SQL ServerSQL Server ( SQL Server 2016 (13.x)SQL Server 2016 (13.x) through current version, SQL DatabaseSQL 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_nameschema_name cannot be specified for DDL or logon triggers.

trigger_nametrigger_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.objectssys.triggerssys.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 函式來查詢它們是否在資料庫中。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.triggersFor 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)