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

本主題適用於:是SQL Server (從 2008 開始)是Azure SQL Database否Azure SQL 資料倉儲 否平行處理資料倉儲 THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)yesAzure SQL DatabasenoAzure 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_nameschema_name cannot be specified for DDL or logon triggers.

trigger_nametrigger_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.

PermissionsPermissions

若要停用 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)