T-SQL 编程之触发器

触发器

一、概念

触发器是一种特殊类型的存储过程，不由用户直接调用。是在对特定的表进行插入、删除、更改操作时自动执行的存储过程。

触发器可以实现主键和外键不能保证的完整性，和数据的一致性。触发器可以实现比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 。

这两个表是无法修改的，触发器执行完成后，就自动删除了。

1. deleted表

delete保存的是delete 删除的数据 以及 update 操作中跟新之前的数据

1. 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