触发器的学习

触发器：

一种特殊类型的存储过程。

不同：存储过程是调用名称执行 || 触发器的调用  通过事件触发 自动调用执行

对表操作：插入，更新、删除 自动执行 触发器。如果定义对应的触发器，系统就会自动调用。

分类：

DDL触发器：create    alter     drop

DML触发器：insert  update  delete

DML触发器分为两种：

after触发器    —— insert   update  delete——操作后触发

instead of触发器 ——insert update delete——不执行定义的操作，而执行触发器的操作。

inserted （插入表）和deleted（删除表）两个表

是逻辑表也是虚表，是系统在内存中创建的，不会存储到数据库中，是只读的，可以读取但不能修改数据，它的结构与操作的表相同。触发器执行过程中存在，并且可以访问，工作完成之后，这两张表会被自动删除

**inserted （插入表）和deleted（删除表）两个表**

对表的操作	inserted	deleted
insert	存放插入的数据	无
update	存放更新后的数据	存放更新前的数据
delete	无	存放被删除的数据

总结

inserted 存放插入或更新后的数据

deleted 存放更新前或删除的数据

触发器执行过程中，都是可以访问的，提交之前，是不是可以撤销呢—-事务回滚？

答 可以，触发器是一个特殊的事务，在这个过程中可以进行一些检查或设定一些条件，如果不满足，可以回滚，撤销操作。

after触发器

对表操作之后触发 不可以去修改 inserted deleted 表的数据，因为它们是只读的

after—— insert:在插入操作执行之后触发。

语法

(after –insert 触发器)

create /alter trigger 触发器名称

on 表名

for/after insert

as

T-SQL语句

go

(after –update触发器)

create /alter trigger 触发器名称

on 表名

for/after update

as

T-SQL语句

go

(after –delete触发器)

create /alter trigger 触发器名称

on 表名

for/after delete

as

T-SQL语句

go

--删除触发器 drop trigger 触发器名称

列子

1(after –insert 触发器)

create table BInfo(
Bid int primary key identity(1,1),
Bname  varchar(50)
)

insert  into  BInfo values('开发'),('运维'),('实施')
create table UserInfo
(
UID  int  primary key identity(1,1),
UserName varchar(50),
Userage int,
Usersex char(2) check(Usersex='男'or Usersex='女') ,
Bid int foreign key  references  BInfo(Bid)
)
insert  into  UserInfo values('王二',23,'女',1),('老吴',30,'男',2),('麻子',24,'男',3)
select *  from  UserInfo,BInfo

--after ---insert 触发器
create  trigger  trigger_userinfo_Insert
on UserInfo
for insert--也可写成 after insert
as
declare @id int,@username varchar(50),@userage int,@usersex char
select @id=UID,@username=UserName,@userage=UserAge,@usersex=UserSex from inserted
print convert(varchar,@id)+','+@username+','+convert(varchar,@userage)+','+@usersex
delete from UserInfo where UID=1
select * from UserInfo
go

--触发trigger_userinfo_Insert
insert  into  UserInfo values('王三',22,'女',1)

2(after –update触发器)

--after--update触发器
--以前执行update语句后，能看到的只是修改后的数据
--使用update触发器可以把修改前和修改后的数据都显示出来
--修改就是先删除原来的数据，再插入一条新的数据
create trigger trigger_update_userinfo
on UserInfo
after update
as
declare @id int,@username varchar(50),@userage int,@usersex char
select @id=UID,@username=UserName,@userage=UserAge,@usersex=UserSex from deleted
print convert(varchar,@id)+','+@username+','+convert(varchar,@userage)+','+@usersex
select @id=UID,@username=UserName,@userage=UserAge,@usersex=UserSex from inserted
print convert(varchar,@id)+','+@username+','+convert(varchar,@userage)+','+@usersex
select * into userinfobackup from deleted--将更新前的数据备份到userinfobackup
select * into userinfobackup1 from inserted--将更新后的数据备份到userinfobackup1
select* from  UserInfo
go

--触发 trigger_update_userinfo
 update UserInfo set UserName='xsb',UserAge=21,UserSex='男' where UID=4

3(after –delete触发器)

注意点补充

1.after –delete 触发器：删除后触发deleted查看删除的数据。一种特殊的存储过程，也是是一种特殊的事务—对操作的数据可做反悔操作

备份 ，查询–打印

2instead of insert 触发器

不执行定义操作，执行的是触发器本身

--after ---delete 触发器
create   trigger  trigger_userinfo_delete
on UserInfo
for delete--也可写成 after insert
as
declare @id int,@username varchar(50),@userage int,@usersex char
select @id=UID,@username=UserName,@userage=UserAge,@usersex=UserSex  from deleted
print convert(varchar,@id)+','+@username+','+convert(varchar,@userage)+','+@usersex
--首先对于备份(将要删除的数据备份到userinfobackup表也就是目标表)
--1看UserInfo 是否含有标识列
--a有的话
--判断是否存在目标表
if(OBJECT_ID('userinfobackup','u') is not null)
--目标表存在
insert into userinfobackup select UID,UserName,UserAge,UserSex,bid from deleted
--目标表不存在
else
select UID,UserName,UserAge,UserSex,bid  into  userinfobackup from deleted

--b没有的话（就不用在意标识列的影响了）
----判断是否存在目标表
--if(OBJECT_ID('userinfobackup','u') is not null)
----目标表存在
--insert into userinfobackup select *  from deleted
----目标表不存在
--else
--select *  into  userinfobackup from deleted
--标识列的缺点：不能显示插入标识列（系统会报 ”仅当使用了列列表并且 IDENTITY_INSERT 为 ON 时，才能为表'userinfobackup'中的标识列指定显式值。“）
go

--触发trigger_userinfo_delete
delete from UserInfo where UID=8

SELECT * FROM UserInfo

4（instead of ---insert 触发器）

--instead of ---insert 触发器
create  trigger  trigger_userinfo_insteadof_insert
on UserInfo
instead of  insert
as
declare @id int,@username varchar(50),@userage int,@usersex char
select @id=UID,@username=UserName,@userage=UserAge,@usersex=UserSex from inserted
print convert(varchar,@id)+','+@username+','+convert(varchar,@userage)+','+@usersex
delete from UserInfo where UID=1
select * from UserInfo
go

--触发trigger_userinfo_Insert
insert  into  UserInfo values('李四',22,'女',1)

--after insert 在触发器中，标识列已经开启自动生成值。（先执行触发的语句再执行触发器内容）
--instead of  insert 在触发器中，标识列并未生成  没有值，要插入的数据却并没有插入（先执行触发器内容，不执行触发的语句）

5（--instead of -- update 触发器 ）

--instead of -- update 触发器
create trigger trigger_insteadof_update
on UserInfo
instead of update
as
declare @id int,@uname varchar(50),@usex char(2),@uage int
select @id=UID,@uname=UserName,@usex=UserSex,@uage=UserAge from deleted
print '更新前的数据'+convert(varchar,@id)+','+@uname+','+convert(varchar,@uage)+','+@usex
--备份更新前的数据
--目标表存在
insert into userinfobackup select UserName,UserAge,UserSex,bid from deleted

select @id=UID,@uname=UserName,@usex=UserSex,@uage=UserAge from inserted
print '更新后的数据'+convert(varchar,@id)+','+@uname+','+convert(varchar,@uage)+','+@usex
go
--触发语句
update UserInfo set UserName='xds'where UID=10

6（--instead of -- delete 触发器 ）

--instead of -- delete 触发器
create  trigger trigger_insteadof_delete
on UserInfo
instead of delete
as
declare @id int,@uname varchar(50),@usex char(2),@uage int
select @id=UID,@uname=UserName,@usex=UserSex,@uage=UserAge from deleted
print '更新前的数据'+convert(varchar,@id)+','+@uname+','+convert(varchar,@uage)+','+@usex
--备份更新前的数据
--目标表存在
insert into userinfobackup1 select UserName,UserAge,UserSex,bid from deleted
go
--触发语句
delete  from UserInfo  where UID=9

--禁用触发器 DISABLE TRIGGER [trigger_userinfo_insteadof_insert] ON UserInfo

总结

instead of 触发器 不执行触发语句的操作，而执行触发器本身的内容

instead of insert 触发器	insert 操作之前触发
instead of update 触发器	update操作之前触发
instead of delete 触发器	delete操作之前触发

after触发器 执行触发语句后再执行触发器本身的内容

instead of insert 触发器	insert 操作之后触发
instead of update 触发器	update操作之后触发
instead of delete 触发器	delete操作之后触发

最后

触发器不是越多越好，对于一个数据表，如果操作数据次数多，定义触发器反而更繁琐 不是好事

如果操作不多，想通过触发器做一些额外的工作，可以合理使用。