如何深入浅析SQL Server 触发器

共计 6375 个字符，预计需要花费 16 分钟才能阅读完成。

这篇文章将为大家详细讲解有关如何深入浅析 SQL Server 触发器，文章内容质量较高，因此丸趣 TV 小编分享给大家做个参考，希望大家阅读完这篇文章后对相关知识有一定的了解。

触发器是一种特殊类型的存储过程，它不同于之前的我们介绍的存储过程。触发器主要是通过事件进行触发被自动调用执行的。而存储过程可以通过存储过程的名称被调用。

  什么是触发器

触发器对表进行插入、更新、删除的时候会自动执行的特殊存储过程。触发器一般用在 check 约束更加复杂的约束上面。触发器和普通的存储过程的区别是：触发器是当对某一个表进行操作。诸如：update、insert、delete 这些操作的时候，系统会自动调用执行该表上对应的触发器。SQL Server 2005 中触发器可以分为两类：DML 触发器和 DDL 触发器，其中 DDL 触发器它们会影响多种数据定义语言语句而激发，这些语句有 create、alter、drop 语句。

DML 触发器分为：

1、after 触发器（之后触发）a、insert 触发器 b、update 触发器 c、delete 触发器  

2、instead of 触发器（之前触发）

其中 after 触发器要求只有执行某一操作 insert、update、delete 之后触发器才被触发，且只能定义在表上。而 instead of 触发器表示并不执行其定义的操作（insert、update、delete）而仅是执行触发器本身。既可以在表上定义 instead of 触发器，也可以在视图上定义。

触发器有两个特殊的表：插入表（instered 表）和删除表（deleted 表）。这两张是逻辑表也是虚表。有系统在内存中创建者两张表，不会存储在数据库中。而且两张表的都是只读的，只能读取数据而不能修改数据。这两张表的结果总是与被改触发器应用的表的结构相同。当触发器完成工作后，这两张表就会被删除。Inserted 表的数据是插入或是修改后的数据，而 deleted 表的数据是更新前的或是删除的数据。

Update 数据的时候就是先删除表记录，然后增加一条记录。这样在 inserted 和 deleted 表就都有 update 后的数据记录了。注意的是：触发器本身就是一个事务，所以在触发器里面可以对修改数据进行一些特殊的检查。如果不满足可以利用事务回滚，撤销操作。

  创建触发器

语法

create trigger tgr_nameon table_namewith encrypion –加密触发器  for update...as Transact-SQL #  创建 insert 类型触发器 -- 创建 insert 插入类型触发器 if (object_id( tgr_classes_insert ,  tr) is not null) drop trigger tgr_classes_insertgocreate trigger tgr_classes_inserton classes for insert -- 插入触发 as -- 定义变量  declare @id int, @name varchar(20), @temp int; -- 在 inserted 表中查询已经插入记录信息  select @id = id, @name = name from inserted; set @name = @name + convert(varchar, @id); set @temp = @id / 2; insert into student values(@name, 18 + @id, @temp, @id); print  添加学生成功！go-- 插入数据 insert into classes values(5 班 , getDate());-- 查询数据 select * from classes;select * from student order by id; insert 触发器，会在 inserted 表中添加一条刚插入的记录。 #  创建 delete 类型触发器 --delete 删除类型触发器 if (object_id( tgr_classes_delete ,  TR) is not null) drop trigger tgr_classes_deletegocreate trigger tgr_classes_deleteon classes for delete -- 删除触发 as print  备份数据中……  if (object_id( classesBackup ,  U) is not null) -- 存在 classesBackup，直接插入数据  insert into classesBackup select name, createDate from deleted; else -- 不存在 classesBackup 创建再插入  select * into classesBackup from deleted; print  备份数据成功！go---- 不显示影响行数 --set nocount on;delete classes where name =  5 班 -- 查询数据 select * from classes;select * from classesBackup; delete 触发器会在删除数据的时候，将刚才删除的数据保存在 deleted 表中。 #  创建 update 类型触发器 --update 更新类型触发器 if (object_id( tgr_classes_update ,  TR) is not null) drop trigger tgr_classes_updategocreate trigger tgr_classes_updateon classes for updateas declare @oldName varchar(20), @newName varchar(20); -- 更新前的数据  select @oldName = name from deleted; if (exists (select * from student where name like  % + @oldName +  %)) begin -- 更新后的数据  select @newName = name from inserted; update student set name = replace(name, @oldName, @newName) where name like  % + @oldName +  %  print  级联修改数据成功！ end else print  无需修改 student 表！go-- 查询数据 select * from student order by id;select * from classes;update classes set name =  五班  where name =  5 班  update 触发器会在更新数据后，将更新前的数据保存在 deleted 表中，更新后的数据保存在 inserted 表中。 # update 更新列级触发器 if (object_id( tgr_classes_update_column ,  TR) is not null) drop trigger tgr_classes_update_columngocreate trigger tgr_classes_update_columnon classes for updateas -- 列级触发器：是否更新了班级创建时间  if (update(createDate)) begin raisError(系统提示：班级创建时间不能修改！, 16, 11); rollback tran; endgo-- 测试 select * from student order by id;select * from classes;update classes set createDate = getDate() where id = 3;update classes set name =  四班  where id = 7;  更新列级触发器可以用 update 是否判断更新列记录； # instead of 类型触发器  instead of 触发器表示并不执行其定义的操作（insert、update、delete）而仅是执行触发器本身的内容。  创建语法 create trigger tgr_nameon table_namewith encryption instead of update...as T-SQL #  创建 instead of 触发器 if (object_id( tgr_classes_inteadOf ,  TR) is not null) drop trigger tgr_classes_inteadOfgocreate trigger tgr_classes_inteadOfon classes instead of delete/*, update, insert*/as declare @id int, @name varchar(20); -- 查询被删除的信息，病赋值  select @id = id, @name = name from deleted; print  id:   + convert(varchar, @id) +  , name:   + @name; -- 先删除 student 的信息  delete student where cid = @id; -- 再删除 classes 的信息  delete classes where id = @id; print  删除 [ id:   + convert(varchar, @id) +  , name:   + @name +   ]  的信息成功！go--testselect * from student order by id;select * from classes;delete classes where id = 7; #  显示自定义消息 raiserrorif (object_id( tgr_message ,  TR) is not null) drop trigger tgr_messagegocreate trigger tgr_messageon student after insert, updateas raisError(tgr_message 触发器被触发 , 16, 10);go--testinsert into student values(lily , 22, 1, 7);update student set sex = 0 where name =  lucy select * from student order by id; #  修改触发器 alter trigger tgr_messageon studentafter deleteas raisError(tgr_message 触发器被触发 , 16, 10);go--testdelete from student where name =  lucy  #  启用、禁用触发器 -- 禁用触发器 disable trigger tgr_message on student;-- 启用触发器 enable trigger tgr_message on student; #  查询创建的触发器信息 -- 查询已存在的触发器 select * from sys.triggers;select * from sys.objects where type =  TR -- 查看触发器触发事件 select te.* from sys.trigger_events te join sys.triggers ton t.object_id = te.object_idwhere t.parent_class = 0 and t.name =  tgr_valid_data -- 查看创建触发器语句 exec sp_helptext  tgr_message  #  示例，验证插入数据 if ((object_id( tgr_valid_data ,  TR) is not null)) drop trigger tgr_valid_datagocreate trigger tgr_valid_dataon studentafter insertas declare @age int, @name varchar(20); select @name = s.name, @age = s.age from inserted s; if (@age   18) begin raisError(插入新数据的 age 有问题 , 16, 1); rollback tran; endgo--testinsert into student values(forest , 2, 0, 7);insert into student values(forest , 22, 0, 7);select * from student order by id; #  示例，操作日志 if (object_id( log ,  U) is not null) drop table loggocreate table log( id int identity(1, 1) primary key, action varchar(20), createDate datetime default getDate())goif (exists (select * from sys.objects where name =  tgr_student_log)) drop trigger tgr_student_loggocreate trigger tgr_student_logon studentafter insert, update, deleteas if ((exists (select 1 from inserted)) and (exists (select 1 from deleted))) begin insert into log(action) values(updated  end else if (exists (select 1 from inserted) and not exists (select 1 from deleted)) begin insert into log(action) values(inserted  end else if (not exists (select 1 from inserted) and exists (select 1 from deleted)) begin insert into log(action) values(deleted  endgo--testinsert into student values( king , 22, 1, 7);update student set sex = 0 where name =  king delete student where name =  king select * from log;select * from student order by id;

关于如何深入浅析 SQL Server 触发器就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。