22 T-SQL 触发器

触发器是一种特殊的存储过程，与存储过程不同的是存储过程通过调用名称来执行，而触发器的调用通过事件触发，自动调用执行。 例如：对表的增删改，如果定义了对应的触发器，载进行增删改时系统会自动调用

分类： DDL触发器：create，alter，drop DML触发器：insert，update，delete

• DML触发器又分为
• after触发器： inter，update，delete操作之后执行
• instead of触发器，insert，update，delete操作不执行，执行触发器中的内容

inserted deleted：插入表 删除表 两者都属于逻辑表，也就是虚表，系统在执行存储过程的时候，在内存中创建，只能读取，必能修改，结构与操作的表相同，触发器执行完成之后，这两个表自动删除

inserted 存放插入或更新后的数据
deleted 存放更新前或删除的数据
触发器执行过程中，都是可以访问的，提交之前，可以撤销，类似于事务的回滚
触发器是一个特殊的事务，在操作数据库表的时候，设置一些条件，如果不满足可以回滚

1.after insert
create trigger trigger_UserInfos_insert
on UserInfos
for insert  --也可以是after insert
as
--T-SQL
   declare @id int,@name varchar(50)
   select @id =UserId,@name=UserName from inserted
   print convert(varchar,@id)+','+@name+"添加成功"
go
--触发trigger_UserInfos_insert
insert into UserInfos(UserName,UserPwd,DeptId,Age)
values('lqq','123',2,23)
2.after update
create trigger trigger_UserInfos_update
on UserInfos
for update
as
declare @id int,@name varchar(50),@age int
select @id=UserId,@name=UserName,@age=Age from deleted
print '修改前：'+convert(varchar,@id)+','+@name+convert(varchar,@age)
select @id=UserId,@name=UserName,@age=Age from inserted
print '修改后：'+convert(varchar,@id)+','+@name+convert(varchar,@age)
select * into UserInfoBackup from deleted--将更新前的数据备份到新表、
go
--触发trigger_UserInfos_update
update UserInfos set UserName="lqq",Age=21
where UserId =22
3.after delete
create trigger trigger_UserInfos_delete
on UserInfo 
for delete
declare @id int,@name varchar(50),@age int
select @id=UserId,@name=UserName,@age=Age from deleted
print '要删除的数据：'+convert(varchar,@id)+','+@name+convert(varchar,@age)
--备份
if(OBJECT_ID('UserInfoBackup','U') is not null)
  --目标表存在
  insert into UserInfoBackup select * from deleted
else
--备份表不存在
  select * into UserinfoBackup from deleted
print "备份成功"
go
--触发
delete from UserInfos where UserId = 22
--标识列备份表不能用select *
4.instead of insert
create trigger trigger_UserInfos_insteadof
on UserInfos
instead of insert
as
declare @id int,@name varchar(50),@age int
select @id=UserId,@name=UserName,@age=Age from inserted
print '要添加的数据：'+convert(varchar,@id)+','+@name+convert(varchar,@age)
go
--触发
insert into UserInfos(UserName,UserPwd,Age,DeptId)
values('lqq01','123',23,2)

after insert：在触发器中，标识列已经自动生成，且有值
instead of insert:在触发器中，标识列并未生成，没有值（0），触发器执行完毕后，要插入的数据并没有插入（插入语句不执行）