Classification
- DML(Data Manipulation Language) Trigger :
- 當(dāng)用戶通過DML事件(對(duì)表或視圖的
insert,update,delete)修改數(shù)據(jù)時(shí)被觸發(fā)。 - 任意合法DML事件發(fā)生都將觸發(fā)DML觸發(fā)器，無論有無表的數(shù)據(jù)行受影響
- DDL(Data Defination Language)Trigger :
- 當(dāng)DDL事件(對(duì)表的
create,alter,drop)發(fā)生時(shí)被觸發(fā)
- Logon Trigger :
- 當(dāng)LOGON事件(建立用戶對(duì)話)時(shí)被觸發(fā)
Syntax
- DML Trigger
-- SQL Server Syntax
-- Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)
CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name
ON { table | view }
[ WITH <dml_trigger_option> [ ,...n ] ]
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > }
<dml_trigger_option> ::=
[ ENCRYPTION ]
[ EXECUTE AS Clause ]
<method_specifier> ::=
assembly_name.class_name.method_name
+ 參數(shù)含義
| Arguments | Meaning | 備注 |
|---|---|---|
WITH ENCRYPTION |
encryption(加密). Obfuscates the text of the CREATE TRIGGER statement. Using WITH ENCRYPTION prevents the trigger from being published as part of SQL Server replication. WITH ENCRYPTION cannot be specified for CLR triggers. | |
EXECUTE AS |
Required for triggers on memory-optimized tables. Enables you to control which user account the instance of SQL Server uses to validate permissions on any database objects that are referenced by the trigger. | |
FOR \ AFTER |
AFTER指明只有在聲明的所有SQL語句完成后此觸發(fā)器才被觸發(fā)。所有參考級(jí)聯(lián)動(dòng)作和約束性檢查都必須成功，才能觸發(fā)此DML觸發(fā)器。 |
AFTER不能被聲明在視圖上。AFTER is the default when FOR is the only keyword specified. |
INSTEAD OF |
指明此DML觸發(fā)器將代替觸發(fā)語句(Triggering SQL Statements)執(zhí)行，即重寫(Overriding)。此參數(shù)不能指明在DDL或logon觸發(fā)器中。 | 每一條表上或視圖上的insert, update, delete語句至多只能有一個(gè)INSTEAD OF觸發(fā)器 |
{ [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] } |
指明觸發(fā)此觸發(fā)器的SQL語句類型，可以為三者的任意組合 | For INSTEAD OF triggers, the DELETE option is not allowed on tables that have a referential relationship specifying a cascade action ON DELETE. Similarly, the UPDATE option is not allowed on tables that have a referential relationship specifying a cascade action ON UPDATE. |
[ WITH APPEND ] |
||
[ NOT FOR REPLICATION ] |
Indicates that the trigger should not be executed when a replication agent modifies the table that is involved in the trigger. |
-- SQL Server Syntax
-- Trigger on an INSERT, UPDATE, or DELETE statement to a table (DML Trigger on memory-optimized tables)
CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name
ON { table }
[ WITH <dml_trigger_option> [ ,...n ] ]
{ FOR | AFTER }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
AS { sql_statement [ ; ] [ ,...n ] }
<dml_trigger_option> ::=
[ NATIVE_COMPILATION ]
[ SCHEMABINDING ]
[ EXECUTE AS Clause ]
- DDL Trigger
-- Trigger on a CREATE, ALTER, DROP, GRANT, DENY, REVOKE or UPDATE statement (DDL Trigger)
CREATE [ OR ALTER ] TRIGGER trigger_name
ON { ALL SERVER | DATABASE }
[ WITH <ddl_trigger_option> [ ,...n ] ]
{ FOR | AFTER } { event_type | event_group } [ ,...n ]
AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier > [ ; ] }
<ddl_trigger_option> ::=
[ ENCRYPTION ]
[ EXECUTE AS Clause ]
- Logon Trigger
-- Trigger on a LOGON event (Logon Trigger)
CREATE [ OR ALTER ] TRIGGER trigger_name
ON ALL SERVER
[ WITH <logon_trigger_option> [ ,...n ] ]
{ FOR| AFTER } LOGON
AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier > [ ; ] }
<logon_trigger_option> ::=
[ ENCRYPTION ]
[ EXECUTE AS Clause ]
