《使用SQL语句创建触发器.ppt》由会员分享，可在线阅读，更多相关《使用SQL语句创建触发器.ppt（25页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、“超市供销存系统超市供销存系统”的数据库的数据库实施和管理实施和管理任务八任务八 创建触发器创建触发器任务任务8：创建：创建触发器触发器理解事务并能灵活使用；理解事务并能灵活使用；理解触发器的作用和分类；理解触发器的作用和分类；能创建能创建INSERT触发器触发器；能创建能创建UPDATE触发器触发器；能创建能创建DELETE触发器触发器。一、事务一、事务事务事务(Transaction)是用户定义的一个数据库操作序列，是用户定义的一个数据库操作序列，这些操作要么全做，要么全不做，这些操作要么全做，要么全不做，是一个不可分割的工作是一个不可分割的工作单位单位。如果某一如果某一事务成功事务成功，
2、则在该事务中进行的所有数据更改均，则在该事务中进行的所有数据更改均会提交，成为数据库中的永久组成部分。如果会提交，成为数据库中的永久组成部分。如果事务遇到错事务遇到错误且必须取消或回滚误且必须取消或回滚，则所有数据更改均被清除。，则所有数据更改均被清除。1.显式事务显式事务每个事务均以每个事务均以 BEGIN TRANSACTION 语句显式开始，以语句显式开始，以 COMMIT 或或 ROLLBACK 语句显式结束。语句显式结束。BEGIN TRANSACTION BEGIN TRANSACTION SQL 语句语句1 SQL 语句语句1 SQL 语句语句2 SQL 语句语句2 .COMMI
3、T ROLLBACK事务结束事务结束COMMIT：事务正常结束事务正常结束 提交事务的所有操作提交事务的所有操作事务中所有对数据库的更新永久生效事务中所有对数据库的更新永久生效ROLLBACK：事务异常终止事务异常终止事务运行的过程中发生了故障，不能继续执行事务运行的过程中发生了故障，不能继续执行回滚事务的所有更新操作回滚事务的所有更新操作事务滚回到开始时的状态事务滚回到开始时的状态2.隐式事务隐式事务当用户没有显式地定义事务时，当用户没有显式地定义事务时，DBMS按缺省规定自动划按缺省规定自动划分事务。分事务。当当 SQL Server 首次执行下列任何语句时，都会自动首次执行下列任何语句时
4、，都会自动启动一个事务：启动一个事务：ALTER TABLE、INSERT、CREATE、OPEN、DELETE、REVOKE、DROP、SELECT、FETCH、TRUNCATE TABLE、GRANT、UPDATE二、触发器概述二、触发器概述1.触发器概念触发器概念触发器是一种特殊的存储过程，它在执行语言事件时自触发器是一种特殊的存储过程，它在执行语言事件时自动生效。动生效。触发器是一段自动执行的程序，并不需要由用户来直接触发器是一段自动执行的程序，并不需要由用户来直接调用；调用；当对表进行当对表进行INSERT、DELETE、UPDATE等操作时，自等操作时，自动激活触发器，并运行其中的
5、动激活触发器，并运行其中的T-SQL语句。语句。每个表可以建立多个触发器，有插入触发器、更新触发每个表可以建立多个触发器，有插入触发器、更新触发器、删除触发器，分别对应于器、删除触发器，分别对应于INSERT、UPDATE和和DELETE操作。也可以将多个操作定义为一个触发器。操作。也可以将多个操作定义为一个触发器。2.触发器优点触发器优点对表的修改合乎业务规则。如、有商品销对表的修改合乎业务规则。如、有商品销售时，销售记录添加后，库存表中该商品售时，销售记录添加后，库存表中该商品的库存量自动减少。的库存量自动减少。实现比实现比CHECKCHECK约束更为复杂的约束。约束更为复杂的约束。CHE
6、CKCHECK约束不允许引用其它表中的字段，约束不允许引用其它表中的字段，而触发器可以。而触发器可以。实现参照完整性约束、数据库中多表的实现参照完整性约束、数据库中多表的级联修改，可使用外键约束或触发器。级联修改，可使用外键约束或触发器。优优点点3.触发器的类别触发器的类别SQL Server2005 包括两大类触发器：包括两大类触发器：DML 触发器和触发器和 DDL 触发器。触发器。（1）DML 触发器在数据库中发生数据操作语触发器在数据库中发生数据操作语言言(DML)事件时将启用。事件时将启用。DML 事件包括在指定事件包括在指定表或视图中修改数据的表或视图中修改数据的 INSERT 语
7、句、语句、UPDATE 语句或语句或 DELETE 语句。语句。（2）DDL 触发器是触发器是 SQL Server 2005 的新的新增功能。当服务器或数据库中发生数据定义语言增功能。当服务器或数据库中发生数据定义语言(DDL)事件时将调用这些触发器。事件时将调用这些触发器。4.INSERTED和和DELETED临时表临时表触发器被激活后，自动创建这两个临时表。这两个临时表存触发器被激活后，自动创建这两个临时表。这两个临时表存放在内存中，不存放在数据库中。触发器执行完成后，两个放在内存中，不存放在数据库中。触发器执行完成后，两个临时表自动删除。临时表自动删除。INSERTED表表:在执行在执
8、行INSERT或或UPDATE语句时，新的数据语句时，新的数据行被添加到数据表中，同时这些行被添加到数据表中，同时这些新的数据行被复制到新的数据行被复制到inserted表表中。中。DELETED表：在执行表：在执行delete或或update语句时，从数据表中语句时，从数据表中删除旧的数据行，然后将这些删除旧的数据行，然后将这些旧的数据行存入旧的数据行存入deleted表中表中。Update操作相当于先执行操作相当于先执行delete操作，然后执行操作，然后执行insert操作。操作。三、三、DMLDML触发器的应用触发器的应用after insert触发器触发器当记录添加到表中后，该触发器
9、激活，创建当记录添加到表中后，该触发器激活，创建inserted临临时表，添加到表中的新记录行同时复制到时表，添加到表中的新记录行同时复制到inserted表。表。after update触发器触发器当表中记录修改后，该触发器激活，创建当表中记录修改后，该触发器激活，创建inserted和和deleted临时表，修改前的旧记录行存入临时表，修改前的旧记录行存入deleted表，表，修改后的新记录行存入修改后的新记录行存入inserted表。表。after delete触发器触发器当表中记录删除后，该触发器激活，创建当表中记录删除后，该触发器激活，创建deleted临时临时表，删除的记录行存入表
10、，删除的记录行存入deleted表。表。1.创建创建after insert触发器触发器创建触发器创建触发器 CREATE TRIGGER 触发器名触发器名 ON 表名表名 AFTER INSERT AS SQL语句语句 创建创建after insert触发器举例触发器举例例、当商品供应进来后，该商品的库存数量需自动增加，例、当商品供应进来后，该商品的库存数量需自动增加，创建触发器实现，触发器名为创建触发器实现，触发器名为add_gy。if exists(select*from sysobjects where name=add_gy and type=tr)drop trigger add_
11、gy gocreate trigger add_gy on 供应供应after insertas declare tx varchar(30),sl real select tx=条形码条形码,sl=供应数量供应数量 from insertedupdate 商品商品 set 库存数量库存数量=库存数量库存数量+sl where 条形码条形码=tx-运行后查看供应表下的触发器运行后查看供应表下的触发器add_gy已存在。已存在。-先运行以下语句查看这两表先运行以下语句查看这两表select*from dbo.供应供应select*from dbo.商品商品-再再运行插入语句，运行插入语句，检验该
12、触发器的正确性检验该触发器的正确性insert into 供应供应(供应号供应号,条形码条形码,供应数量供应数量,供应时间供应时间,进进价价)values(20100123005,6930504300198,60,2010-6-12,3.10)-再查看这两表再查看这两表2.创建创建after delete触发器触发器CREATE TRIGGER 触发器名触发器名 ON 表名表名 AFTER DELETE AS SQL语句语句当表中记录删除后，该触发器激活，创建当表中记录删除后，该触发器激活，创建deleted临时表，临时表，删除的记录行存入删除的记录行存入deleted表。表。删除的记录删除的
13、记录 DELETED表表创建创建after delete触发器举例触发器举例例、若某顾客回来退某商品，该如何实现例、若某顾客回来退某商品，该如何实现这类操作？销售记录删除后，商品表中该这类操作？销售记录删除后，商品表中该商品的库存数量要自动能修改。创建触发商品的库存数量要自动能修改。创建触发器实现，触发器名为器实现，触发器名为del_xs。if exists(select*from sysobjects where name=del_xs and type=tr)drop trigger del_xsgocreate trigger del_xs on 进行销售进行销售 for delete
14、asdeclare tx varchar(30),sl realselect tx=条形码条形码,sl=销售数量销售数量 from deletedupdate 商品商品 set 库存数量库存数量=库存数量库存数量+sl where tx=条形条形码码 go-检验该触发器的正确性检验该触发器的正确性 select*from 进行销售进行销售 select*from 商品商品 delete from 进行销售进行销售 where 销售号销售号=101-106-44 and 条形码条形码=6930504300198 select*from 进行销售进行销售 select*from 商品商品过程：过程
15、：delete from 进行销售进行销售 where 销售号销售号=101-106-44 and 条条形码形码=6930504300198进行销售进行销售表表有一个有一个触发器触发器del_xs是是一个一个after delete 触发器触发器当表中记录删除后，该触发器激活，创建当表中记录删除后，该触发器激活，创建deleted临时表，临时表，删除的记录行存入删除的记录行存入deleted表。表。select tx=条形码条形码,sl=销售数量销售数量 from deleted 结果是结果是tx=6930504300198，,sl=10update 商品商品 set 库存数量库存数量=+sl
16、 where tx=条形码条形码将商品表的将商品表的条形码条形码为为6930504300198的记录的的记录的库存数量库存数量增增加加103.创建创建after update触发器触发器CREATE TRIGGER 触发器名触发器名 ON 表名表名 AFTER UPDATE ASIF UPDATE(字段名字段名)AND|OR UPDATE(字段名字段名)BEGIN SQL语句语句 END 使用：使用：IF UPDATE(字段名字段名)AND|OR UPDATE(字段名字段名)，则当修改该字段的值时该触发器触发，则当修改该字段的值时该触发器触发。如果不使用，则修改任何一个字段值时都会触发。如果不
17、使用，则修改任何一个字段值时都会触发。UPDATE触发器触发器该触发器激活，创建该触发器激活，创建inserted和和deleted临时表，修改前的临时表，修改前的旧记录行存入旧记录行存入deleted表，修改后的新记录行存入表，修改后的新记录行存入inserted表。表。原有行原有行 DELETED表中，表中，修改后的新行修改后的新行 INSERTED表中。表中。create trigger xiu_ss on 进行销售进行销售for update as if update(销售数量销售数量)begin SQL语句语句 end四、触发器管理四、触发器管理SP_HELP 触发器名触发器名查看触发器的一般信息，如触发器的名称、属性、创查看触发器的一般信息，如触发器的名称、属性、创建时间、类型等。建时间、类型等。SP_HELPTEXT 触发器名触发器名显示触发器的源代码。显示触发器的源代码。SP_DEPENDS 触发器名触发器名显示和触发器相关的数据库对象。显示和触发器相关的数据库对象。重命名和删除触发器重命名和删除触发器重命名触发器重命名触发器SP_RENAME 旧触发器名，新触发器名旧触发器名，新触发器名删除触发器删除触发器DROP TRIGGER 触发器名触发器名