赞
踩
触发器也是和之前写的数据库函数和存储过程类似,
可以看做是一种特殊的数据库函数,只不过由系统调用,我们也需要表明调用的实际和规则
1.对于主体在表和视图的触发器
Trigger on an INSERT, UPDATE, or DELETE statement to a table or view
可以在Insert,Update,delete时触发
不能在Select触发
2.对于主体在数据库和服务器的触发器
Trigger on a CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE statement
可以在CREATE, ALTER, DROP, GRANT, DENY, REVOKE,UPDATE时触发
ALTER TRIGGER schema_name.trigger_name ON ( table | view ) [ WITH <dml_trigger_option> [ ,...n ] ] ( FOR | AFTER | INSTEAD OF ) --( FOR | AFTER | INSTEAD OF )代表触发器执行的时机 --FOR的默认既是AFTER表示在原命令执行后执行 --INSTEAD OF代表不执行原命令,直接去执行触发器内容 { [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] } --从机修改不执行触发该触发器 [ 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
示例:
测试表
Create table t_student(
Id int IDENTITY(1,1) PRIMARY key,
Name varchar(20),
Birth datetime,
Address varchar(100)
)
INSERT into t_student values('xiaoming','2022-07-07','GuangDong')
INSERT into t_student values('xiaohuang','2022-07-06','FuJian')
AFTER触发器
create trigger TestAfterTrigger
ON t_student
AFTER UPDATE
AS
BEGIN
PRINT '修改了一条记录'
END
更新语句正常执行
INSTEAD OF触发器
Create Trigger TestTriggerSec
On t_student
INSTEAD OF DELETE
AS
BEGIN
PRINT '你根本删除不了'
END
再去查询原表
指定的记录并没有被删除,即原命令并没有执行
这就是INSTAD OF和AFTER的区别
ALTER TRIGGER trigger_name
ON { DATABASE | ALL SERVER }
[ WITH <ddl_trigger_option> [ ,...n ] ]
{ FOR | AFTER } { event_type [ ,...n ] | event_group }
AS { sql_statement [ ; ] | EXTERNAL NAME <method specifier>
[ ; ] }
}
<ddl_trigger_option> ::=
[ ENCRYPTION ]
[ <EXECUTE AS Clause> ]
<method_specifier> ::=
assembly_name.class_name.method_name
数据库/服务器触发器(DDL触发器)的语法和表/视图触发器几乎一样,
不同在于不能使用INSTAED OF,和触发的规则不一样
示例:
Create Trigger DBTrigger
On DATABASE
FOR CREATE_TABLE
AS
PRINT '你创建了一张表'
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。