你的位置:首页 > 数据库

[数据库]15、SQL Server 触发器


SQL Server 触发器

触发器是一种特殊的存储过程,只有当试图用数据操作语言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