sql触发器语法.

第七章——触发器本节要点A认识触发器B触发器的分类C触发器的创建D使用触发器E触发器练习的巩固典型的应用就是银行的取款机系统帐户信息表bank交易信息表transInfo张三取钱200问题：没有自动修改张三的余额最优的解决方案就是采用触发器：–它是一种特殊的存储过程–也具备事务的功能–它能在多表之间执行特殊的业务规则张三开户1000元,李四开户1元为什么需要触发器一、认识触发器1、触发器的概述定义：触发器是在对指定表插入(insert)、更新(update)或删除(delete)操作时所自动执行的存储过程。作用：实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性。触发器是一种高级约束，可以定义比用check约束更为复杂的约束–可执行复杂的SQL语句（if/while/case）–可引用其它表中的列–所有数据值均正确的状态一、认识触发器相似：触发器与存储过程相似，触发器也是SQL语句集。区别：触发器不能用execute语句调用，而是在用户执行Transact-SQL语句自动执行。2、触发器与存储过程的区别运行方式区别：存储过程用户应用程序或触发器调用并执行触发器特定事件，自动执行二、触发器的分类1、DDL触发器定义：当服务器或者数据库中发生数据定义语言（DDL）时触发器将被调用。DDL数据定义语言：创建，修改或删除数据库中各种对象，包括表，视图，索引等。以下操作可以使用DDL触发器：1、要防止对数据库架构进行某些修改3、要记录数据架构中的更改或者事件。2、希望数据库中发生某种情况以响应数据库架构中的修改二、触发器的分类1、DML触发器定义：当数据库服务器中发生数据操作语言（DML）事件时要执行的操作。DML数据定义语言：对已存在数据库表进行元组插入，删除，修改。2、DML触发器的用途(1)DML触发器可通过数据库中相关表实现级联修改，通过级联引用完整性约束可以更有效的更改。(2)DML触发器可以防止恶意或者错误insert，update，以及delete操作，并强制执行比check约束定义的限制更为复杂的其他限制。(3)DML触发器可以评估数据修改前后表的状态，并根据该差异采取措施。(4)一个表的多个同类DML触发器，允许采取多个不同的操作来响应同一个修改语句。二、触发器的分类触发器触发时创建了DELETED表和INSERED表只读，不允许修改；触发器执行完成后，自动删除1、delete表delete表存放由执行delete或者update语句而要从表中删除所有行，在执行delete或update被删除的行从触发触发器表中移动到delete表中。2、insert表insert表存放由执行insert或者update语句而要向表中插入的所有的行，在执行insert或update新的行同时添加到触发器的表和insert表中，insert表的内容是触发触发器的表新行的副本。二、触发器的分类修改操作inserted表deleted表增加(INSERT)记录存放新增的记录------删除(DELETE)记录-----存放被删除的记录修改(UPDATE)记录存放更新后的记录存放更新前的记录inserted表和deleted表存放的信息三、触发器创建语法createtrigger触发器名称on表名/视图名for/after/instedof--触发的时机delete/insert/update--触发器的类型assql语句end例：在student数据库的‘学员信息表’表中创建一个stuinfo_delete触发器createtriggerstuinfo_deleteon学员信息表fordeleteasdelete成绩表from成绩表a,deletedbwherea.学号=b.学号四、DML触发器1、insert触发器createtriggerstuinfo_insert--创建触发器on学生表afterinsertasupdatestuinfo--插入年龄set年龄=datediff（year，s.生日，getdate（））from学生表ainnerjoininsertedbwherea.学号=b.学号--after之insert触发器insert触发器对目标表执行insert语句时，就会调用触发器。例如我们添加一个年龄列，这个触发器的名称为stuinfo_insert插入一个新的学员信息，以验证触发器是否执行insertintostuinfovalue（'0002','mike','男','1993-01-25','null'）select*fromstuinfotransInfocardIDtransTypetransMoney1001000210010002存入300存入500insertedcardIDtransTypetransMoney10010001支取200transInfocardIDtransTypetransMoney1001000210010002存入300存入50010010001支取200插入记录行触发insert触发器。向inserted表中插入新行的副本触发器检查inserted表中插入的新行数据，确定是否需要回滚或执行其他操作INSERT触发器的工作原理：四、DML触发器1、delete触发器定义：当触发delete触发器时，从表中删除的行将会放置在一个特殊的delete表中，delete跟deleted表一样是个临时表。区别：delete触发器并不执行truncatetable语句，原因在于日志不记录truncatetable例题：删除数据库【student】中【学员信息】表时，相应的考试成绩备份表也会删除,使用delete语句删除002这个学员信息，验证触发器就会自动执行，并删除学员成绩备份表中的信息createtriggerstuinfo_delete--创建触发器on学员信息fordeleteasdelete成绩表from成绩表a,deletedbwherea.学号=b.学号insert成绩表values（'0002',90,95）select*from成绩表deletefrom学员信息wherestuno='0001'select*from成绩表transInfocardIDtransTypetransMoney1001000210010002存入300存入50010010001支取200transInfocardIDtransTypetransMoney1001000210010002存入300存入500deletedcardIDtransTypetransMoney10010001支取200删除记录行触发delete触发器向deleted表中插入被删除的副本触发器检查deleted表中被删除的数据，决定是否需要回滚或执行其他操作DELETE触发器的工作原理：四、DML触发器update触发器定义：当一个update语句在目标表运行的时候就调用更新触发器，例题：数据库【student】中【stuinfo】表中生产日期的改变，对应学员的年龄也发生改变createtriggerstuinfo_update--创建触发器onstuinfoafterstuinfoasifupdate(birthday)beginupdatestuinfo---执行SQL语句setstuage=datediff(year,s.birthday,getdate())fromstuinfosinnerjoininsertdions.stuno=i.stunoend四、DDL触发器定义：当服务器或者数据库中发生数据定义语言（DDL）时触发器将被调用。DDL数据定义语言：创建，修改或删除数据库中各种对象，包括表，视图，索引等。例题：如何使用DDL触发器来防止在【student】数据库中表被修改删除的操作，首先要在【student】数据库中定义一个数据库的DDL触发器。createtriggerTRIG_DDL---创建一个DDL触发器fordrop_table,drop_view---删除表或删除视图ondatabase---数据库asbeginprint'无法修改或者删除表，请在之前禁用或删除DDL触发器TRIG_DDL！'ROLLBACKTRANSACTION---采用回滚的办法以达到阻止操作的目的end嵌套触发器如果一个触发器在执行操作时引发了另一个触发器，而这个触发器又接着引发下一个触发器，那么就形成了触发器的嵌套。任何触发器都可以包含影响另一个表的UPDATE、INSERT或者DELETE语句。嵌套触发器在安装时就被启用可以使用系统存储过程sp_configure禁用和重新启用嵌套触发器最多可以嵌套32层禁用嵌套EXECsp_configure‘nestedtriggers’,0启用嵌套EXECsp_configure‘nestedtriggers’,1管理触发器查看触发器：可以使用像sp_helptext、sp_help和sp_depends等系统存储过程来查看触发器的有关信息修改触发器：修改触发器的语法与创建触发器的语法一样，只要把create改为alter即可删除触发器：禁用DML触发器：禁用DDL触发器：禁用所有触发器：启用触发器：只要将DISABLE变为ENABLE即可execsp_helptextTRIG_DDL--查看触发器的内容AltertriggerTRIG_DDL……droptriggerTRIG_DDLDISABLETRIGGERtri_stuinfo_updateONstuInfoDISABLETRIGGERTRIG_DDLONDATABASEDISABLETRIGGERALLONALLSERVERENABLETRIGGERtri_stuinfo_updateONstuInfo--启用触发器ENABLETRIGGERtrig_DDLONDATABASE--启用数据库触发器ENABLETRIGGERALLONALLSERVER--启用以同一作用域定义的所有触发器不能在触发器中使用的语句触发器中可以使用大多数T-SQL语句，但如下一些语句是不能在触发器中使用的。CREATE语句，如：CREATEDATABASE、CREATETABLE、CREATEINDEX等。ALTER语句，如：ALTERDATABASE、ALTERTABLE、ALTERINDEX等。DROP语句，如：DROPDATABASE、DROPTABLE、DROPINDEX等。DISK语句，如：DISKINIT、DISKRESIZE。LOAD语句，如：LOADDATABASE、LOADLOG。RESTORE语句，如：RESTOREDATABASE、RESTORELOG。RECONFIGURETRUNCATETABLE语句在sybase的触发器中不可使用！触发器是在对表进行插入、更新或删除操作时自动执行的存储过程，触发器通常用于强制业务规则触发器还是一个特殊的事务单元，当出现错误时，可以执行ROLLBACKTRANSACTION回滚撤销操作触发器一般都需要使用临时表：deleted表和inserted表，它们存放了被删除或插入的记录行副本触发器类型：–DML触发器–DDL触发器触发器总结课后复习作业1、触发器有哪几种？2、触发器的作用是什么？3、分别用insert触发器，update触发器，delete触发器做一个例题。