sql触发器(使用及语法实例)

sql触发器

触发器（trigger）是个特殊的存储进程，它的执行不是由程序调用，也不是手工发动，而是由事情来触发，比方当对一个表进行操作（insert，delete，update）时就会激活它执行。触发器常常用于加强数据的完整性束缚和业务规矩等。触发器能够从DBA_TRIGGERS，USER_TRIGGERS数据字典中查到。
触发器能够查询其他表，并且能够包含杂乱的SQL语句。

sql触发器使用及语法实例

MicrosoftSQLServer?2000供给了两种主要机制来强制业务规则和数据完整性：束缚和触发器。触发器是一种特别类型的存储进程，它不同于之前的我们介绍的存储进程。触发器主要是经过事情进行触发被主动调用履行的。而存储进程能够经过存储进程的称号被调用。
什么是触发器
触发器对表进行刺进、更新、删去的时分会主动履行的特别存储进程。触发器一般用在check束缚愈加杂乱的束缚上面。触发器和普通的存储进程的区别是：触发器是当对某一个表进行操作。比如：update、insert、delete这些操作的时分，体系会主动调用履行该表上对应的触发器。SQLServer2005中触发器能够分为两类：DML触发器和DDL触发器，其间DDL触发器它们会影响多种数据界说语言句子而激发，这些句子有create、alter、drop句子。
长处
触发器可经过数据库中的相关表实现级联更改；经过级联引证完整性束缚能够更有效地履行这些更改。
触发器能够强制比用CHECK束缚界说的束缚更为杂乱的束缚。
与CHECK束缚不同，触发器能够引证其它表中的列。例如，触发器能够使用另一个表中的SELECT比较刺进或更新的数据，以及履行其它操作，如修正数据或显示用户界说错误信息。
触发器也能够评价数据修正前后的表状况，并依据其差异采取对策。
DML触发器分为：
1、after触发器（之后触发）
a、insert触发器
b、update触发器
c、delete触发器
2、insteadof触发器（之前触发）
其间after触发器要求只有履行某一操作insert、update、delete之后触发器才被触发，且只能界说在表上。而insteadof触发器表明并不履行其界说的操作（insert、update、delete）而仅是履行触发器自身。既能够在表上界说insteadof触发器，也能够在视图上界说。
触发器有两个特别的表：刺进表（instered表）和删去表（deleted表）。这两张是逻辑表也是虚表。有体系在内存中创立者两张表，不会存储在数据库中。而且两张表的都是只读的，只能读取数据而不能修正数据。这两张表的成果总是与被改触发器应用的表的结构相同。当触发器完结工作后，这两张表就会被删去。Inserted表的数据是刺进或是修正后的数据，而deleted表的数据是更新前的或是删去的数据。
SQLServer触发器实例详解
Update数据的时分便是先删去表记载，然后增加一条记载。这样在inserted和deleted表就都有update后的数据记载了。注意的是：触发器自身便是一个业务，所以在触发器里面能够对修正数据进行一些特别的检查。假如不满足能够使用业务回滚，撤销操作。
?创立触发器
语法
createtriggertgr_nameontable_namewithencrypion–加密触发器forupdate…asTransact-SQL
#创立insert类型触发器
–创立insert刺进类型触发器if(object_id(‘tgr_classes_insert’,’tr’)isnotnull)droptriggertgr_classes_insertgocreatetriggertgr_classes_insertonclassesforinsert–刺进触发as–界说变量declare@idint,@namevarchar(20),@tempint;–在inserted表中查询现已刺进记载信息select@id=id,@name=namefrominserted;set@name=@name+convert(varchar,@id);set@temp=@id/2;insertintostudentvalues(@name,18+@id,@temp,@id);print’增加学生成功！’;go–刺进数据insertintoclassesvalues(‘5班’,getDate());–查询数据select*fromclasses;select*fromstudentorderbyid;
insert触发器，会在inserted表中增加一条刚刺进的记载。
#创立delete类型触发器
–delete删去类型触发器if(object_id(‘tgr_classes_delete’,’TR’)isnotnull)droptriggertgr_classes_deletegocreatetriggertgr_classes_deleteonclassesfordelete–删去触发asprint’备份数据中……’;if(object_id(‘classesBackup’,’U’)isnotnull)–存在classesBackup，直接刺进数据insertintoclassesBackupselectname,createDatefromdeleted;else–不存在classesBackup创立再刺进select*intoclassesBackupfromdeleted;print’备份数据成功！’;go—-不显示影响行数–setnocounton;deleteclasseswherename=’5班’;–查询数据select*fromclasses;select*fromclassesBackup;
delete触发器会在删去数据的时分，将刚才删去的数据保存在deleted表中。
#创立update类型触发器
–update更新类型触发器if(object_id(‘tgr_classes_update’,’TR’)isnotnull)droptriggertgr_classes_updategocreatetriggertgr_classes_updateonclassesforupdateasdeclare@oldNamevarchar(20),@newNamevarchar(20);–更新前的数据select@oldName=namefromdeleted;if(exists(select*fromstudentwherenamelike’%’+@oldName+’%’))begin–更新后的数据select@newName=namefrominserted;updatestudentsetname=replace(name,@oldName,@newName)wherenamelike’%’+@oldName+’%’;print’级联修正数据成功！’;endelseprint’无需修正student表！’;go–查询数据select*fromstudentorderbyid;select*fromclasses;updateclassessetname=’五班’wherename=’5班’;
update触发器会在更新数据后，将更新前的数据保存在deleted表中，更新后的数据保存在inserted表中。
#update更新列级触发器
if(object_id(‘tgr_classes_update_column’,’TR’)isnotnull)droptriggertgr_classes_update_columngocreatetriggertgr_classes_update_columnonclassesforupdateas–列级触发器：是否更新了班级创立时间if(update(createDate))beginraisError(‘体系提示：班级创立时间不能修正！’,16,11);rollbacktran;endgo–测验select*fromstudentorderbyid;select*fromclasses;updateclassessetcreateDate=getDate()whereid=3;updateclassessetname=’四班’whereid=7;
更新列级触发器能够用update是否判别更新列记载；
#insteadof类型触发器
insteadof触发器表明并不履行其界说的操作（insert、update、delete）而仅是履行触发器自身的内容。
创立语法
createtriggertgr_nameontable_namewithencryptioninsteadofupdate…asT-SQL
#创立insteadof触发器
if(object_id(‘tgr_classes_inteadOf’,’TR’)isnotnull)droptriggertgr_classes_inteadOfgocreatetriggertgr_classes_inteadOfonclassesinsteadofdelete/*,update,insert*/asdeclare@idint,@namevarchar(20);–查询被删去的信息，病赋值select@id=id,@name=namefromdeleted;print’id:’+convert(varchar,@id)+’,name:’+@name;–先删去student的信息deletestudentwherecid=@id;–再删去classes的信息deleteclasseswhereid=@id;print’删去[id:’+convert(varchar,@id)+’,name:’+@name+’]的信息成功！’;go–testselect*fromstudentorderbyid;select*fromclasses;deleteclasseswhereid=7;
#显示自界说音讯raiserror
if(object_id(‘tgr_message’,’TR’)isnotnull)droptriggertgr_messagegocreatetriggertgr_messageonstudentafterinsert,updateasraisError(‘tgr_message触发器被触发’,16,10);go–testinsertintostudentvalues(‘lily’,22,1,7);updatestudentsetsex=0wherename=’lucy’;select*fromstudentorderbyid;
#修正触发器
altertriggertgr_messageonstudentafterdeleteasraisError(‘tgr_message触发器被触发’,16,10);go–testdeletefromstudentwherename=’lucy’;
#启用、禁用触发器
–禁用触发器disabletriggertgr_messageonstudent;–启用触发器enabletriggertgr_messageonstudent;
#查询创立的触发器信息
–查询已存在的触发器select*fromsys.triggers;select*fromsys.objectswheretype=’TR’;–检查触发器触发事情selectte.*fromsys.trigger_eventstejoinsys.triggerstont.object_id=te.object_idwheret.parent_class=0andt.name=’tgr_valid_data’;–检查创立触发器句子execsp_helptext’tgr_message’;
#示例，验证刺进数据
if((object_id(‘tgr_valid_data’,’TR’)isnotnull))droptriggertgr_valid_datagocreatetriggertgr_valid_dataonstudentafterinsertasdeclare@ageint,@namevarchar(20);select@name=s.name,@age=s.agefrominserteds;if(@age<18)beginraisError(‘刺进新数据的age有问题’,16,1);rollbacktran;endgo–testinsertintostudentvalues(‘forest’,2,0,7);insertintostudentvalues(‘forest’,22,0,7);select*fromstudentorderbyid;
#示例，操作日志
if(object_id(‘log’,’U’)isnotnull)droptableloggocreatetablelog(idintidentity(1,1)primarykey,actionvarchar(20),createDatedatetimedefaultgetDate())goif(exists(select*fromsys.objectswherename=’tgr_student_log’))droptriggertgr_student_loggocreatetriggertgr_student_logonstudentafterinsert,update,deleteasif((exists(select1frominserted))and(exists(select1fromdeleted)))begininsertintolog(action)values(‘updated’);endelseif(exists(select1frominserted)andnotexists(select1fromdeleted))begininsertintolog(action)values(‘inserted’);endelseif(notexists(select1frominserted)andexists(select1fromdeleted))begininsertintolog(action)values(‘deleted’);endgo–testinsertintostudentvalues(‘king’,22,1,7);updatestudentsetsex=0wherename=’king’;deletestudentwherename=’king’;select*fromlog;select*fromstudentorderbyid;