一、触发器概念
--触发器(trigger)是个特殊的存储过程,它的执行并不需要我们去显式调用,而是由一些事件触发,这有点类似C#中的事件处理机制。当使用UPDATE,INSERT 或DELETE的一种或多种对指定的数据库的相关表进行操作时,会触发触发器。
--常见触发器
1.DML触发器
- insert,update,delete任意操作,如果我们对该表写了对应的DML触发器,那么该触发器自动执行。
2.After触发器(for):
- 1.语句执行完毕以后触发
- 2.按语句触发,而不是所影响的行数,无论影响多少行,只触发一次
- 3.只能建立在常规表上,不能建立在视图和临时表上
- 4.可以递归触发,最高可达32级
- 5.Update(列),在update语句触发时,判断某列是否被更新,返回bool值
3.instead of触发器(不支持before):更新视图时多个表一起更新
- 1.用来替换原本的操作
- 2.不会递归触发
- 3.可以在约束被检查之前触发
- 4.可以建立在表和视图上
4.(*)DDL触发器: 修改表,修改列,新增表,新增列等。它在数据库结构发生变化时执行,我们主要用它来记录数据库的修改过程,以及限制程序员对数据库的修改,比如不允许删除某些指定表等。
二、触发器的操作
1.创建
CREATE TRIGGER triggerName ON tableName
After(for) |instead of Update |insert|delete
as BEGIN --do something END
select top 0 * into beitaiUser from Users
create trigger [dbo].[tri_update]
on [dbo].[Users]
after delete
as
begin
insert into beitaiUser(userName,Password,Sex,Birthday,Telephone)
select userName,Password,Sex,Birthday,Telephone from deleted
end
GO
select * from beitaiUser
eg:创建一个insert触发器:创建一张Books_sum表,用于存储图书数量,每次在Books表中插入图书以后,都重新计算图书数量并更新到Books_sum表中。
CREATE trigger [dbo].[trr_insert] on [dbo].[Books]
after insert
as
begin
if object_id('Book_sum','U') is null --判断Book_sum是否存在
create table Book_Sum(BooksCount int default (0)); --创建Book_sum表
declare @Bookscont int;
select @Bookscont=Count(*) from Books;
if(not exists(select * from Book_Sum)) --判断表中是否有记录
insert Book_Sum values(0);
update Book_Sum set BooksCount=@Bookscont; --把更新后的图书数量插入Book_sum表中
end
GO
eg:创建一个update触发器:每次更新表Authors中的AuthorID和AuthorName以后,都显示出更新前的信息和更新后的信息
create trigger [dbo].[trr_update]
on [dbo].[Authors]
after update
as
begin
declare @BkCount int;
select @BkCount=count(*) from Authors;
update Book_Sum set BooksCount =@BkCount;
select AuthorID as 更新前作者编号,AuthorName as 更新前作者姓名 from deleted
select AuthorID as 更新后作者编号,AuthorName as 更新后作者姓名 from inserted
end
GO
2.理解触发器里面的两个临时的表:Deleted , Inserted 。
注意Deleted 与Inserted分别表示触发事件的表“旧的一条记录”和“新的一条记录”。
一个数据库系统中有两个虚拟表用于存储在表中记录改动的信息,分别是:
虚拟表Inserted 虚拟表Deleted
注意:
- v尽量避免在触发器中执行耗时操作,因为触发器会与SQL语句在同一个事务中(事务不结束就无法释放锁)。
- v避免在触发器中做复杂操作
- v触发器编写时注意对多行触发时的处理。(一般不建议使用游标,性能问题!)