SQL学习 - 触发器的理解以及使用触发器简析触发器使用触发器扩展使用使用心得

98 阅读 0 评论 65 点赞

我是靠谱客的博主不安大船，这篇文章主要介绍SQL学习 - 触发器的理解以及使用触发器简析触发器使用触发器扩展使用使用心得，现在分享给大家，希望可以做个参考。

触发器简析

触发器是一种特殊类型的存储过程，它会在指定的表/视图数据发生变化时自动执行，一般用于INSERT、UPDATE、DELETE操作配合使用。
PS：之前团队遇到过繁琐存储过程优化，想做成事务中包含触发器配合使用，但是发现其执行方式是阻塞式的，并没有做到实际优化（后来采用SQL消息队列的方式进行异步处理，此处不再扩展讨论，后续进行深入探讨）。

触发器使用

简单触发器语法为：create trigger A on Table(View) for delete(insert/update) as SQL
使用示例如下：
1.建测试表，插入两条数据

复制代码

1
2
3
4
CREATE TABLE Student(PKID int IDENTITY(1,1) NOT NULL,Name nvarchar(50) not null,Age int)
insert into Student values('张三','18')
insert into Student values('李四','19')

2.建立触发器（delete时触发）

复制代码

1
2
3
4
5
6
create trigger TrigDeleteStu
on Student
for delete 
as 
  insert into Student values('刪除','0')

3.效果测试

复制代码

1
2
3
4
select * from student 
delete student where Name='李四'
select * from student

结果如下：
第一次查询：表中有张三、李四数据；
删除动作后，触发器执行！
第二次查询：李四被删除，表中有张三、删除数据。
在这里插入图片描述

触发器扩展使用

一、DELETED、INSERTED表

触发器在执行时会产生两张表：DELETED和INSERTED，分别是删除表和插入表。
在这两张表中，我们可以看到一些正在操作的数据！
扩展示例如下：
1.建测试表，插入两条数据（语句如上，不再赘述）；
2.建立触发器（delete时触发）

复制代码

1
2
3
4
5
6
7
8
9
create trigger TrigDeleteStu
on Student
for delete 
as 
begin
  select * from deleted
  insert into Student values('刪除','0')
end

3.效果测试

复制代码

1
2
delete student where Name='李四'

结果如下：
虽然只执行了删除操作，但是触发器中存在查询语句，并且将删除动作的数据从deleted表中查找了出来！
在这里插入图片描述

二、FOR 、AFTER、 Instead of区别

For、After
for和after的效果一样，都是SQL语句操作执行后才去执行触发器内容。
Instead of
Insead of是将SQL语句操作指令交给触发器去判断执行，即系统不直接对表执行操作，而是把操作内容交给触发器，让触发器自己判断语句执行是否有误，从而进行其他正常语句执行还是回滚事务。

具体示例如下：
1.建学生表和成绩表（包含主外键关系）

复制代码

1
2
3
CREATE TABLE Student(StudentNo nvarchar(50) PRIMARY KEY,Name nvarchar(50) not null,Age int)
CREATE TABLE Mark(PKID int IDENTITY(1,1) NOT NULL,StudentNo nvarchar(50) FOREIGN KEY REFERENCES Student(StudentNo),SubjectName nvarchar(50),Mark int )

2.分别插入几条测试数据

复制代码

1
2
3
4
5
6
insert into Student VALUES('A001','張三',18),('A002','李四',17),('A003','王五',19)
insert into Mark (StudentNo,SubjectName,Mark)
values('A001','語文',50),('A001','數學',100),('A001','英語',60),
      ('A002','語文',90),('A002','數學',50),('A002','英語',80),
      ('A003','語文',95),('A003','數學',98),('A003','英語',90)

3.建立FOR类型触发器并测试结果

复制代码

1
2
3
4
5
6
7
8
9
create trigger ForTrigDelStu
on Student
for delete 
as 
begin
  delete Mark where StudentNo in(select StudentNo from deleted)
end
delete student where Name='李四'

结果如下：
由于Mark外键是student 的主键，两表相互连接，此时就会出错！
在这里插入图片描述
4.建立Instead of类型触发器并测试结果

复制代码

1
2
3
4
5
6
7
8
9
create trigger InsteadTrigDelStu
on Student
instead of delete 
as 
begin
  delete Mark where StudentNo in(select StudentNo from deleted)
end
delete student where Name='李四'

结果如下：
成功执行两表删除操作！
在这里插入图片描述
这里需要特别注意的是：Instead of触发器只是对操作语句的一个判断，真正执行的动作只是触发器里面的内容！！！
即：上述触发器删除的只是Mark表中李四的成绩，但是student表中的李四数据仍然存在，这部分需要特别注意下！

使用心得

1.常用触发器的原因一般是：操作某一表数据时需要对其他表进行联合操作。
如：学生表删除学生，同时需要删除成绩表、选课表等其他表中该学生的数据。
2.使用触发器注意For和Instead Of的使用区别！
3.目前我使用的触发器类型一般是DML类型触发器（表数据的插入、更新、删除），另外还有DDL类型（建表/视图、改表/视图、删表/视图），由于使用较少了解不多，在此就不多扩展了，以后有机会再深入学习吧。