转载

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_name on { table | view } [ with encryption ] { for | after | instead of } { insert , update , delete } as sql_statement

创建insert触发器

if(object_id('tgr_test_insert','tr') is not null)  drop trigger tgr_test_insert --删除触发器 go create trigger tgr_test_insert on student --创建在student表  for insert --insert触发 as declare @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' end insert 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')

15、SQL Server 触发器

查看运行结果

15、SQL Server 触发器

创建update触发器

if(object_id('tgr_student_update','tr') is not null)  drop trigger tgr_student_update go create trigger tgr_student_update on student  for update as  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_delete go create trigger tgr_student_delete on student  for delete as  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 student  select * from student select * from studentBackup

15、SQL Server 触发器

创建instead of触发器

if(object_id('tgr_student_inteadof','tr') is not null)  drop trigger tgr_student_inteadof go create trigger tgr_student_inteadof on student  instead of delete -- update , insert as  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_insert on student for insert as print 'ok'

禁用触发器

disable trigger tgr_test_insert on student

启用触发器

enable trigger tgr_test_insert on student

查询已创建的触发器

select * from sys.triggers
正文到此结束
Loading...