使用SQL语句创建触发器

“超市供销存系统”的数据库实施和管理

创建触发器任务8：创建触发器理解事务并能灵活使用；理解触发器的作用和分类；能创建INSERT触发器；能创建UPDATE触发器；能创建DELETE触发器。任务描述一、事务事务(Transaction)是用户定义的一个数据库操作序列，这些操作要么全做，要么全不做，是一个不可分割的工作单位。如果某一事务成功，则在该事务中进行的所有数据更改均会提交，成为数据库中的永久组成部分。如果事务遇到错误且必须取消或回滚，则所有数据更改均被清除。1.显式事务每个事务均以BEGINTRANSACTION语句显式开始，以COMMIT或ROLLBACK语句显式结束。

BEGINTRANSACTION

BEGINTRANSACTIONSQL语句1SQL语句1

SQL语句2SQL语句2……….……….

COMMITROLLBACK事务结束COMMIT：事务正常结束提交事务的所有操作事务中所有对数据库的更新永久生效ROLLBACK：事务异常终止事务运行的过程中发生了故障，不能继续执行回滚事务的所有更新操作事务滚回到开始时的状态2.隐式事务当用户没有显式地定义事务时，DBMS按缺省规定自动划分事务。当SQLServer首次执行下列任何语句时，都会自动启动一个事务：ALTERTABLE、INSERT、CREATE、OPEN、DELETE、REVOKE、DROP、SELECT、FETCH、TRUNCATETABLE、GRANT、UPDATE二、触发器概述1.触发器概念触发器是一种特殊的存储过程，它在执行语言事件时自动生效。触发器是一段自动执行的程序，并不需要由用户来直接调用；当对表进行INSERT、DELETE、UPDATE等操作时，自动激活触发器，并运行其中的T-SQL语句。每个表可以建立多个触发器，有插入触发器、更新触发器、删除触发器，分别对应于INSERT、UPDATE和DELETE操作。也可以将多个操作定义为一个触发器。2.触发器优点对表的修改合乎业务规则。如、有商品销售时，销售记录添加后，库存表中该商品的库存量自动减少。实现比CHECK约束更为复杂的约束。CHECK约束不允许引用其它表中的字段，而触发器可以。实现参照完整性约束、数据库中多表的级联修改，可使用外键约束或触发器。优点3.触发器的类别SQLServer2005包括两大类触发器：DML触发器和DDL触发器。（1）DML触发器在数据库中发生数据操作语言(DML)事件时将启用。DML事件包括在指定表或视图中修改数据的INSERT语句、UPDATE语句或DELETE语句。（2）DDL触发器是SQLServer2005的新增功能。当服务器或数据库中发生数据定义语言(DDL)事件时将调用这些触发器。4.INSERTED和DELETED临时表触发器被激活后，自动创建这两个临时表。这两个临时表存放在内存中，不存放在数据库中。触发器执行完成后，两个临时表自动删除。INSERTED表:在执行INSERT或UPDATE语句时，新的数据行被添加到数据表中，同时这些新的数据行被复制到inserted表中。DELETED表：在执行delete或update语句时，从数据表中删除旧的数据行，然后将这些旧的数据行存入deleted表中。Update操作相当于先执行delete操作，然后执行insert操作。三、DML触发器的应用afterinsert触发器当记录添加到表中后，该触发器激活，创建inserted临时表，添加到表中的新记录行同时复制到inserted表。afterupdate触发器当表中记录修改后，该触发器激活，创建inserted和deleted临时表，修改前的旧记录行存入deleted表，修改后的新记录行存入inserted表。

afterdelete触发器当表中记录删除后，该触发器激活，创建deleted临时表，删除的记录行存入deleted表。1.创建afterinsert触发器创建触发器CREATETRIGGER

触发器名

ON表名

AFTERINSERT

AS SQL语句

创建afterinsert触发器举例例、当商品供应进来后，该商品的库存数量需自动增加，创建触发器实现，触发器名为add_gy。ifexists(select*fromsysobjectswherename=‘add_gy'andtype='tr')droptriggeradd_gygocreatetriggeradd_gyon供应afterinsertasdeclare@txvarchar(30),@slrealselect@tx=条形码,@sl=供应数量frominsertedupdate商品set库存数量=库存数量+@slwhere条形码=@tx--运行后查看供应表下的触发器add_gy已存在。--先运行以下语句查看这两表select*fromdbo.供应select*fromdbo.商品--再运行插入语句，检验该触发器的正确性insertinto供应(供应号,条形码,供应数量,供应时间,进价)values('20100123005','6930504300198',60,'2010-6-12',3.10)--再查看这两表2.创建afterdelete触发器CREATETRIGGER

触发器名

ON表名

AFTERDELETE

AS SQL语句当表中记录删除后，该触发器激活，创建deleted临时表，删除的记录行存入deleted表。删除的记录

DELETED表创建afterdelete触发器举例例、若某顾客回来退某商品，该如何实现这类操作？销售记录删除后，商品表中该商品的库存数量要自动能修改。创建触发器实现，触发器名为del_xs。ifexists(select*fromsysobjectswherename='del_xs'andtype='tr')droptriggerdel_xsgocreatetriggerdel_xson进行销售

fordeleteas declare@txvarchar(30),@slreal select@tx=条形码,@sl=销售数量fromdeleted update商品set库存数量=库存数量+@slwhere@tx=条形码

go--检验该触发器的正确性select*from进行销售select*from商品deletefrom进行销售where销售号='101-106-44'and条形码='6930504300198'select*from进行销售select*from商品过程：deletefrom进行销售where销售号='101-106-44'and条形码='6930504300198‘进行销售表有一个触发器del_xs是一个afterdelete触发器当表中记录删除后，该触发器激活，创建deleted临时表，删除的记录行存入deleted表。select@tx=条形码,@sl=销售数量fromdeleted

结果是@tx=‘6930504300198‘，,@sl=10update商品set库存数量=+@slwhere@tx=条形码将商品表的条形码为‘6930504300198‘的记录的库存数量增加103.创建afterupdate触发器CREATETRIGGER触发器名ON表名 AFTERUPDATE AS IFUPDATE(字段名)[AND|ORUPDATE(字段名)]

BEGIN SQL语句

END使用：IFUPDATE(字段名)[AND|ORUPDATE(字段名)]，则当修改该字段的值时该触发器触发。如果不使用，则修改任何一个字段值时都会触发。UPDATE触发器该触发器激活，创建inserted和deleted临时表，修改前的旧记录行存入deleted表，修改后的新记录行存入inserted表。原有行DELETED表中，修改后的新行INSERTED表中。createtriggerxiu_sson进行销售

forupdate asif