触发器
一、概念
触发器是一种特殊类型的存储过程,不由用户直接调用。是在对特定的表进行插入、删除、更改操作时自动执行的存储过程。
触发器可以实现主键和外键不能保证的完整性,和数据的一致性。触发器可以实现比check约束更为复杂的约束。
二、格式
CREATE TRIGGER trigger_name
ON {table_name | view_name}
{FOR | After | Instead of } [ insert, update,delete ]
AS
sql_statement
// 在对学生删除时,随即把成绩表中的该学生的成绩也删除。
CREATE TRIGGER delete_stu
ON student
AFTER DELETE
AS
DELETE grade FROM grade a , DELETED b
WHERE a.id = b.id
三、触发器类型
触发器的分类可以大致分为两种,一种是DDL触发器
、一种时DML触发器
。
- DDL触发器:当服务器或者是数据库发生DDL数据定义语言时触发。DDL定义语言: 创建、修改、删除数据库中的各种对象,不如视图、索引、表等。
- DML触发器: 当服务器或者是数据库发生DML数据定义语言时触发。DML定义语言: 数据中的表的元组的插入、删除、修改。
INSERT / DELETE
触发器触发的时候创建了两个表,INSERTED
和 DELETED
。
这两个表是无法修改的,触发器执行完成后,就自动删除了。
- deleted表
delete保存的是delete 删除的数据 以及 update 操作中跟新之前的数据
- inserted表
inserted中保存了inser插入的新的数据以及update 操作中更新的数据。
总之 insert跟后有关,delete后跟前有关
1、 After
触发器
After
触发器要求只有执行某一操作insert、update、delete
之后触发器才被触发,且只能定义在表上。
1)`insert`触发器
// 插入一个工作,那么初始化在这个工作的最小工资为1000
CREATE TRIGGER Job_insert
ON Job
AFTER INSERT
AS
UPDATE Job SET minMoney = 1000 FROM Job , INSERTED
WHERE Job.jobId = inserted.jobId;
// 插入
INSERT INTO Job VALUES(0005,0 ,0);
// 查询
2)`update`触发器
3)`delete`触发器
// 删除职工表的一个职工,然后将该职工号的工作在Job表中删除。
CREATE TRIGGER deletejob
ON Worker
AFTER DELETE
AS
DELETE Job FROM Job a, DELETED b
WHERE a.jobId = b.jobId
2、Instead of
触发器
Instead of
触发器表示并不执行其定义的操作(insert、update、delete)
而仅是执行触发器本身。既可以在表上定义instead of
触发器,也可以在视图上定义。
四、实例
后触发
// 插入和更新时如果基本工资不在该工作的工资范围内,就操作失败
CREATE TRIGGER base_ok
ON Worker AFTER INSERT,UPDATE
AS
BEGIN
IF EXISTS (
SELECT * FROM Worker a JOIN Job b
ON a.jobId = b.jobId
WHERE base BETWEEN b.minMoney AND b.maxMoney
)
ROLLBACK
END
GO