SQL笔记--(7)--[触发器]

Classification

• DML(Data Manipulation Language) Trigger :

• 当用户通过DML事件(对表或视图的insert, update, delete)修改数据时被触发。
• 任意合法DML事件发生都将触发DML触发器，无论有无表的数据行受影响

• DDL(Data Defination Language)Trigger :

• 当DDL事件(对表的create, alter, drop)发生时被触发

• Logon Trigger :

• 当LOGON事件(建立用户对话)时被触发

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

+　参数含义

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语句完成后此触发器才被触发。所有参考级联动作和约束性检查都必须成功，才能触发此DML触发器。	AFTER不能被声明在视图上。AFTER is the default when FOR is the only keyword specified.
INSTEAD OF	指明此DML触发器将代替触发语句(Triggering SQL Statements)执行，即重写(Overriding)。此参数不能指明在DDL或logon触发器中。	每一条表上或视图上的insert, update, delete语句至多只能有一个INSTEAD OF触发器
{ [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] }	指明触发此触发器的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 ]