首页 > 代码库 > 触发器操作
触发器操作
创建触发器
语法
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;
# 显示自定义消息raiserror
if (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;
重命名触发器
1.用查询分析器重命名
exec sp_rename 原名称, 新名称
sp_rename 是 SQL Server™ 自带的一个存储过程,用于更改当前数据库中用户创建的对象的名称,如表名、列表、索引名等。
2.用企业管理器重命名
在表上点右键->“所有任务”->“管理触发器”,选中所要重命名的触发器,修改触发器语句中的触发器名称,点击“确定”。
Sql After触发器总结
第一:触发器不会对每条记录都触发一次。例如我们在一次事务中更新或者删除了一条以上的记录,触发器只会触发一次,但能正常反映所有受影响的行。
第二:使用触发器一定要清楚两个虚似表:
1:inserted,事务中被插入的所有记录。
2:deleted,事务中被删除的所有记录。
说明:数据库中不存在updated表,因为数据的更新是先删除然后插入的过程。有了上面两个虚似表,我们就可以在受影响的行上做其它操作,例如在录入积分酒店数据后,利用inserted中的数据信息来更新本记录的某些字段值。
第三:after,从字面意义来看应该是某个操作之后的意思,after触发器就是数据成功插入数据表后执行的操作,如果插入数据失败,对应的触发器是不会引发的。
第四:触发器与开发人员在对于什么样才是对表的成功操作概念上有不同的定义,当更新或者是删除0条记录时,触发器也会被触发,但对于开发人员来说,显然这种触发是没有意义的。此种触发第一是没有意义,第二是占用系统资源,如果你的数据服务器非常繁忙,而且有用户正在锁定数据库表,执行触发器的时间往往比你想象的要多。所以我们希望遇到这种情况应该尽快退出触发器。我们可以用如下代码来完成,此代码一定要在解发器语句的开头部分体现,否则@@rowcount会受到前面代码的影响。
BEGIN
PRINT ‘触发器退出‘
RETURN
END
第四:不要在存储过程中使用select以及print语句,虽然这两个语句在调试过程中非常好用,但最好不要体现在正式上线环境中,这些语句会产生另外的结果集,如果没有正常处理它们,或者是没有预料到它们的出现,那么这种结果集有可以影响到客户端应用程序的失败。
第五:触发器管理。
1:利用sp_helptrigger命令来查看一个表关联的触发器。sp_helptrigger 表名
2:利用sp_helptext命令来查询某一个触发器的代码。sp_helptest 触发器名。
3:删除触发器,和删除数据库其它对象类似,drop trigger 触发器名。
4:修改触发器和生成触发器内容差不多,只不过修改的语句为alter trigger。
5:利用exec sp_rename命令来完成触发器的重命名。
第六:触发器中的事务管理。触发器始终是对它进行初始化事务的一部分,这个事务可以是显式的也可以是隐式的。可以在触发器中使用如下脚本来回滚之前的数据操作。在这之前的代码中并不一定要显示的写begin transaction之类的代码。
BEGIN
ROLLBACK TRANSACTION
END
第七:触发器的执行顺序,对于after触发器来说,一个表可以对应多个after触发器,众多触发器如何来控制它们的执行顺序呢?我们可以利用exec sp_settrggerorder命令来完成,示命如下:
@order=‘first,或者是last或者是none‘,
@stmttype=‘数据操作类型,例如insert,update或者是delete‘
第八:哪些情况下根本不需要使用触发器:
1:如果能够使用约束实现的功能,就不要使用触发器。
2:如果可以使用存储过程来完成,那么也不分使用触发器。
说明:触发器的实现,调试,管理比起存储过程都更加困难,如果有更好的方式实现你的需求最好不要使用触发器,除非没有比触发器更简单的方法。
触发器操作