15、SQL Server 触发器

SQLServer触发器

触发器是一种特殊的存储过程，只有当试图用数据操作语言DML来修改数据时才会触发，DML包含对视图和表的增、删、改。

触发器分为DML触发器和DDL触发器，其中DML触发器会对数据表，视图进行insert、update、delete的时候触发。DDL则是create、alter、drop等时触发。

DML触发器又分为after（之后触发）和insert of（之前触发）触发器。

如果在创建触发器的时候不指定类型，则默认是after触发器。

触发器有两个虚拟的临时表：插入表（inserted）和删除表（deleted），由系统在内存中创建，不会保存到数据库。并且两张表都是只读的。当触发器完成工作后，这两张表会自动删除。

增加：inserted表 记录

删除：deleted表 记录

修改：inserted表记录修改前 deleted表记录修改后

修改数据时现删掉该记录，然后再增加一天新的记录。这样inserted和updated就都存在记录了。

触发器本身就是一个事务，可以利用事务的回滚，撤销操作。事务就是要么都执行，要么一条都不执行。

创建触发器

语法：

create trigger [shema_name . ] trg_nameon { table | view }[ with encryption ]{ for | after | instead of }{ insert , update , delete }assql_statement

创建insert触发器

if(object_id('tgr_test_insert','tr') is not null)    drop trigger tgr_test_insert --删除触发器gocreate trigger tgr_test_inserton student --创建在student表    for insert --insert触发asdeclare @id int,@name varchar(20),@sex char(2),@age int--查询inserted表中已经插入的信息select @id = id,@name = name,@sex = sex, @age = age from inserted--用查到的信息加入到会员表中if(@age >30) --如果年龄不符合 则修改begin    update student set age = 30 where id = @id    PRint '年龄太大，已自动修改为30'endinsert into [user] (userName,passWord,RoleId) values (@name,@sex + cast(@age as varchar),3)print '添加学生成功！';print '添加会员成功！'

对student表创建触发器后对该表执行insert后

insert into student (name,sex,age,mail) values ('海盗船长','男',88,'853020304@QQ.com')

查看运行结果

创建update触发器

if(object_id('tgr_student_update','tr') is not null)    drop trigger tgr_student_updategocreate trigger tgr_student_updateon student    for updateas    declare @oldName varchar(10),@newName varchar(10)    select @oldName = name from deleted; --查找更新前的数据    select @newName = name from inserted;--查找更新后的数据    if(@oldName = @newName)    begin        print '数据相同'        rollback tran;   --回滚 不执行修改操作    end    else        print '修改成功'

update student set name='海盗船长' 

事务在触发器中结束。批处理已中止。

update student set name='粉红娘娘'

修改成功

创建delete触发器

if(object_id('tgr_student_delete','tr')is not null)    drop trigger tgr_student_deletegocreate trigger tgr_student_deleteon student    for deleteas    if(exists(select * from sys.databases where name = 'studentBackup'))        insert into studentBackup select name,sex,age,mail from deleted;    else        print '不存在，创建再插入'        create table studentBackup        (            name varchar(20),            sex char(2),            age int,            mail varchar(20)        )        insert into studentBackup select name,sex,age,mail from deleted;

delete from studentselect * from studentselect * from studentBackup

创建instead of触发器

if(object_id('tgr_student_inteadof','tr') is not null)    drop trigger tgr_student_inteadofgocreate trigger tgr_student_inteadofon student    instead of delete -- update , insertas    declare @id int,@name varchar(20)    select @id = id,@name = name from deleted;    --instead of 先触发    --先删除user表信息    delete from [user] where userName = @name;    --再删除student表信息    delete from [student] where id = @id;delete from student where name = '海盗船长'

修改触发器

alter trigger tgr_test_inserton studentfor insertasprint 'ok'

禁用触发器

disable trigger tgr_test_insert on student

启用触发器

enable trigger tgr_test_insert on student

查询已创建的触发器

select * from sys.triggers