SQL SERVER创建触发器

最新推荐文章于 2024-07-22 17:53:44 发布

用户名不能存在

最新推荐文章于 2024-07-22 17:53:44 发布

阅读量796

点赞数 1

文章标签：数据库

本文链接：https://blog.csdn.net/m0_64604195/article/details/128120142

版权

本文深入探讨了数据库中的触发器类型，包括AFTER触发器、INSTEAD OF触发器以及DDL触发器。AFTER触发器在DML操作完成后执行，INSTEAD OF触发器则在操作前执行，允许替代默认行为。DDL触发器则针对数据库结构更改事件，如创建、修改或删除表等。了解这些触发器的语法和用法对于数据库管理和应用程序设计至关重要。

摘要由CSDN通过智能技术生成

AFTER 触发器语法：

 1 CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name   
 2 ON { table }   
 3 [ WITH <dml_trigger_option> [ ,...n ] ]  
 4 { FOR | AFTER }   
 5 { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }   
 6 AS { sql_statement  [ ; ] [ ,...n ] }  
 7 
 8 <dml_trigger_option> ::=  
 9     [ NATIVE_COMPILATION ]  
10     [ SCHEMABINDING ]  
11     [ EXECUTE AS Clause ]

INSTEAD OF 触发器语法：

 1 CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name   
 2 ON { table | view }   
 3 [ WITH <dml_trigger_option> [ ,...n ] ]  
 4 { FOR | AFTER | INSTEAD OF }   
 5 { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }   
 6 [ WITH APPEND ]  
 7 [ NOT FOR REPLICATION ]   
 8 AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > }  
 9 
10 <dml_trigger_option> ::=  
11     [ ENCRYPTION ]  
12     [ EXECUTE AS Clause ]  
13 
14 <method_specifier> ::=  
15     assembly_name.class_name.method_name

DDL 触发器语法：

1 CREATE [ OR ALTER ] TRIGGER trigger_name   
2 ON { ALL SERVER | DATABASE }   
3 [ WITH <ddl_trigger_option> [ ,...n ] ]  
4 { FOR | AFTER } { event_type | event_group } [ ,...n ]  
5 AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }  
6 
7 <ddl_trigger_option> ::=  
8     [ ENCRYPTION ]  
9     [ EXECUTE AS Clause ]

登陆触发器语法：

1 CREATE [ OR ALTER ] TRIGGER trigger_name   
2 ON ALL SERVER   
3 [ WITH <logon_trigger_option> [ ,...n ] ]  
4 { FOR| AFTER } LOGON    
5 AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }  
6 
7 <logon_trigger_option> ::=  
8     [ ENCRYPTION ]  
9     [ EXECUTE AS Clause ]