sqlserver触发器详解：sqlserver触发器after/for和instead of的区别详解（实例讲解），触发器定义创建操作打通，触发器的优缺点，触发器使用建议

是谢添啊

已于 2022-11-23 12:49:08 修改

阅读量1.1w

点赞数 39

分类专栏： # SQLServer 文章标签： sqlserver 数据库 sql

于 2022-10-10 12:01:53 首次发布

本文链接：https://blog.csdn.net/qq_62982856/article/details/127242109

版权

SQLServer 专栏收录该内容

2 篇文章 3 订阅

订阅专栏

1.触发器介绍

触发器（Trigger）是用户定义在关系表上的一类由事件驱动的特殊过程

由服务器自动激活
可以进行更为复杂的检查和操作，具有更精细和更强大的数据控制能力

📝 示例

CREATE TRIGGER tri_StudentInsDel 
ON [dbo].[T_Student] 
FOR INSERT, DELETE 
AS
SELECT * FROM T_Student

当对表T_Student的数据进行插入或删除操作时，触发器“tri_StudentUpdate”将会自动执行。

2.特点

定义在表上的一个对象
一种特殊类型的存储过程，同时触发器可以调用更多的存储过程。
不需要专门语句调用，通过事件进行触发而被执行
当执行insert、delete和update语句时自动被触发执行
触发器可以在有数据操作时自动强制执行某些业务规则
触发器执行的内容可以是报警、维护数据的完整性、特殊的数据处理
触发器可以用于完整性检查，但比约束、默认值、规则等功能强大、灵活

3.DML触发器分类

DML触发器是一些附加在特定表或视图上的操作代码，当数据库服务器中发生数据操作语言事件时执行这些操作。SqlServer中的DML触发器有三种：

insert触发器：向表中插入数据时被触发
update触发器：修改表中数据时被触发
delete触发器：从表中删除数据时被触发

4.inserted表与deleted表

4.1 表介绍

触发器有两个特殊的表：插入表（inserted表）和删除表（deleted表）。
这两张是逻辑表也是虚表。有系统在内存中创建者两张表，不会存储在数据库中。
而且两张表的都是只读的，只能读取数据而不能修改数据。
这两张表的结果总是与被改触发器应用的表的结构相同。当触发器完成工作后，这两张表就会被删除。

4.2 创建时机

Inserted：存放触发事件的SQL语言所 插入后或 更新后的那条新记录
Deleted：存放更新前和删除后的那条旧记录

5.定义触发器

5.1 定义触发器

create trigger 触发器名
On 表或视图
{ {for | after} | instead of}  触发事件
as
触发代码

5.2 分类

我们先定义class和student表，以这个两个表来具体说明两类触发器的区别。

-- 先创建class班级表
create table class(
	class_id int primary key,
	class_name varchar(16) not null
);

-- 再创建student学生表
create table student(
	stu_id int primary key,
	stu_name varchar(12) not null,
	class_id int not null,
	constraint foregin_stu_class foreign key(class_id) references class(class_id) 
);

我们再往表中添加几条测试记录：

insert into class(class_id,class_name) values
(1,'浪班'),
(2,'雨班');

insert into student(stu_id,stu_name,class_id) values
(20212880,'狐狸半面添',1),
(20212881,'是谢添啊',1),
(20212882,'晨夏',2);

5.2.1 AFTER（for）触发器

它是在执行INSERT、UPDATE、DELETE语句操作之后执行触发器操作。它主要是用于记录变更后的处理或检查，一旦发生错误，可以用Rollback Transaction语句来回滚本次事件，不过不能对视图定义AFTER触发器。

📝 先来个简单实例：在往class表添加班级记录时，触发操作打印新添加班级的id号

go
create trigger pri_add_classas
on class
for insert
as
	declare @class_id int
	select @class_id=class_id from inserted
	print @class_id
go

📝 再来个值得探讨的：删除class表中某个班级的记录，导致触发操作student表对应class_id的学生记录一并删除

go 
create trigger pri_del_class
on class
for delete
as
	-- 删除student表中的记录
	delete from student 
	where class_id = (select class_id from deleted)
go

看似没什么问题，当我们执行上述语句的时候的确也是创建成功的，那我们来删除一条班级记录试试

delete from class where class_id = 1;

❌ 提示报错了

可能这时就有疑问了，为什么呢？我们不是已经写了触发器吗，当删除class表中记录时会删除student对应class_id的学生记录。

这是因为我们采用的after(for)触发器，它的特点是在执行完数据操作后再执行触发器里的操作。

在这题中，就是先删除class表中的再删除student中的学生记录
而我们又建立了外键约束，必须先删除外键的记录才能删除依赖表的记录，也就是说，我们必须先删除student表的记录再删除class表的记录。

所以这样看来，我们创建的 after触发器与 外键约束 相冲突了，导致删除失败。外键约束我们已经写好了，那我们应该相办法改变触发器，怎么才能让触发器先执行删除student表记录再删除class表记录呢？这里就引出了我们的 instead of触发器。

由于这个触发器是没有意义的，达不到我们的目的，为了不影响后面的操作，建议先把该没意义的触发器删除
drop trigger pri_del_class;

5.2.2 INSTEAD OF触发器

如同单词一样，可以理解为代替的意思,INSTEAD OF 触发器用来代替通常的触发动作。

当你在一张表上定义了这样的触发器后，如果对表做insert、update、delete操作时触发了所定义的触发器，他就会直接转到触发器去执行触发器里定义的事件，不在执行之前做的insert、update、delete操作了。

对数据的操作只是一个“导火索”而已，真正起作用的是触发器里面的动作，用于触发触发器的DML语句不会生效；往往这种触发器会有很多分支判断语句在里面，根据不用的条件做不同的动作。

因此，INSTEAD OF 触发器的动作要早于表的约束处理，而INSTEAD OF触发器是可以定义在视图上的。

📝 解决 5.2.1 中的问题：让触发器先执行删除student表记录再删除class表记录

请注意：我们在上面对 instead of 触发器的说明中已经强调了触发触发器中操作的数据操作不会再生效，因此我们必须在触发器中增加对class表的记录删除操作。

go 
create trigger pri_del_class
on class
instead of delete
as
	-- 先删除
	delete from student 
	where class_id = (select class_id from deleted)
	
	-- 由于触发触发器的数据操作不再生效，因此我们必须在触发器中进行删除class表中的记录操作
	delete from class
	where class_id = (select class_id from deleted)
go

执行一下创建触发器语句，再进行删除class表记录的操作

-- 这条删除class表记录的操作是不生效的，它的作用仅仅是触发触发器操作
-- 真正的删除class表记录的操作实际是在触发器中完成的
delete from class where class_id = 1;

✅ 运行一下delete操作，再查看class表与student表，明显是操作成功的，达到了我们想要的效果！

5.3 知识准备 - 变量声明与赋值

声明变量
```
declare @变量名  数据类型[,…]
```

赋值

select @变量名=值     
-- 或 
SET  @局部变量名=表达式

5.4 实例演示

5.4.1 行触发器

1️⃣ 在教师表T上建立一个触发器，检查更新或者新插入的行，若是教授且薪水低于3000，则保证其薪水为3000，每更新或插入一行，都会触发as后面的代码

go
create trigger tr_t_sal
on T
for insert,update
as
	declare @title varchar(10),@salary money
	select @title=T.title,@salary=T.salary
	from T join inserted i on T.tno = i.ino
	if(@title='教授') and ((@salary<3000) or (@salary is null))
	begin
		update T set salary=3000 
		where title='教授' and tno=((select tno from inserted)
	end
go

2️⃣ 在学生课程数据库的Student表上创建一个触发器tri_StudentSnoUpdate，当对学号列进行修改时，给出提示信息并取消修改操作。

UPDATE(COLUMNNAME) 函数的使用，如果更新的是指定的COLUMN，就会返回 TRUE

go
CREATE TRIGGER tri_StudentSdeUpdate
ON Student
FOR UPDATE
AS
  DECLARE @text varchar(50)
  IF UPDATE(sno)
   BEGIN 
    SET  @text='学生数据被修改!!!'
    RAISERROR(@text,16,1)
    ROLLBACK TRANSACTION
   END 
go

RAISERROR(@text,16,1)的解释：

RAISERROR返回用户定义的错误信息并设系统标志，记录发生错误。
并给出严重级别(1～18)和错误调用状态(1～127 ) 。这里分别取“16”和“1”。

3️⃣ 在s_c_info表上建立一个触发器tr_updasc，用于监控对成绩的更新，要求更新后的成绩不能比更新前低，如果新成绩低则取消操作，给出提示信息，否则允许更新。

go
create trigger tr_updasc
on s_c_info
for update
as
	declare @old_score numeric(5,1),@new_score numeric(5,1)
	select @old_score=score from deleted
	select @new_score=score from inserted
	if(@new_score<@old_score)
		begin
			print'新成绩不能比旧成绩低'
			rollback transaction
		end
go

5.4.2 因对表1的操作触发更新表2

1️⃣ 员工管理数据库中有部门表和员工表，部门表中的“部门人数”的值，随着员工表记录的增减而增减

go
create trigger tr_indel_emp
on 员工表
for insert,update
as
	update 部门表
	set 部门人数 = 部门人数 + 1
	where 部门号 = (select 部门号 from inserted)
	update 部门号
	set 部门人数 = 部门人数 - 1
	where 部门号 = (select 部门号 from deleted)
go

2️⃣ 若修改员工表中的所在部门，如何让部门表中相关两个部门的人数发生变化

go
create trigger tr_up_dep
on 员工表
for update
as
	update 部门表
	set 部门人数 = 部门人数 + 1
	where 部门号 = (select 部门号 from inserted)
	update 部门号
    set 部门人数 = 部门人数 - 1
    where 部门号 = (select 部门号 from deleted)
go

3️⃣ 对教师表T的salary字段做了任何修改或插入新行，则将该操作痕迹作为一条记录插入到sal_log表中

go
create trigger tr_sal_log
on T
for insert,update
as 
	if update(salary)
		begin
			if(select count(*) from deleted) <> 0
				begin
					insert into sal_log(type,tno,oldsal,newsal)
                    select 'update',t.tno,d.salary,i.salary
                    from t,inserted i,deleted d
                    where t.tno = i.ino and i.ino = d.dno
                end
            else
            	begin
            		insert into sal_log(type,tno,oldsal)
            		select 'delete',tno,salary
            		from deleted
            	end
        end
go

5.4.3 instead of 替代触发器

较多用于基于视图的操作触发一段代码，用这段代码“替代”原本对于视图的SQL命令，原本的SQL命令不再执行因为视图若涉及多个表，则不允许更新或插入

go
create view v_tc -- 创建教师任课祝图
as 
select tc.tno, t. tname, tc.cno, c.cnamefrom t, tc, c
where t. tno=tc.tno and tc.cno=c.cnogo
go

-- 因视图涉及3个表，不能更新数据
insert into v_tc values ('T20', 'Mary','C20','English')
go

6.激活触发器

触发器的执行，是由触发事件激活的，并由数据库服务器自动执行

7.删除触发器

7.1 基本语法

触发器必须是一个已经创建的触发器，并且只能由具有相应权限的用户删除。

删除触发器的SQL语法：

DROP TRIGGER <触发器名> ON <表名>;

7.2 实例演示

📝 删除教师表Teacher上的触发器Insert_Sal

DROP TRIGGER Insert_Sal ON Teacher;

8.其它补充

8.1 管理触发器

1️⃣ 查看系统表中触发器对象的信息

select * from sysobjects where xtype='TR'
go

2️⃣ 使用sp_help系统过程查看触发器的一般信息

sp_help 触发器名
go

3️⃣ 查看触发器的正文信息

sp_helptext 触发器名
go

8.2 删除触发器

if exists (select name from sysobjectswhere name='触发器名'and xtype='TR')
	begin
        drop trigger tr_t_sal
    end
go

8.3 修改触发器

基本语法：

alter trigger 触发器名
on 表名
{ {for | after} | instead of}  触发事件
as
触发操作

9.触发器优缺点

9.1 优点

强化约束：强制复杂业务的规则和要求，能实现比check语句更为复杂的约束。
跟踪变化：触发器可以侦测数据库内的操作，从而禁止数据库中未经许可的更新和变化。
级联运行：侦测数据库内的操作时，可自动地级联影响整个数据库的各项内容。
嵌套调用：触发器可以调用一个或多个存储过程。触发器最多可以嵌套32层。

9.2 缺点

可移植性差。
占用服务器资源，给服务器造成压力。
执行速度主要取决于数据库服务器的性能与触发器代码的复杂程度。
因为难于调试，容易造成不可预料的错误
嵌套调用一旦出现问题，排错困难，而且数据容易造成不一致，后期维护不方便。

10.触发器使用建议

尽量避免在触发器中执行耗时操作，因为触发器会与SQL语句认为在同一事务中，事务不结束，就无法释放锁。
避免在触发器中做复杂操作，影响触发器性能的因素比较多（Eg:产品版本，所使用的架构等），要想编写高效的触发器考虑因素比较多，编写高性能触发器还是很难的。
触发器编写时注意多行触发时的处理。（一般不建议使用游标）
触发器数目不宜太多，反而会增加服务器工作量，加大开销
手工修改数据的时侯，根据实际情况暂时关闭触发器
尽量不要出现触发器触发其他触发器的情况，太复杂，容易出错