《SQL Server 2005数据库任务化教程 梁竞敏 黄华林 宋广科 等编著 任务六》由会员分享，可在线阅读，更多相关《SQL Server 2005数据库任务化教程 梁竞敏 黄华林 宋广科 等编著 任务六（30页珍藏版）》请在金锄头文库上搜索。
1、SQL Server 2005数据库任务化教程,梁竞敏 黄华林 宋广科 等编著,中国水利水电出版社,任务六 触发器的规划与设计,一、任务目标,1掌握创建和使用触发器来实现数据有效性和完整性； 2 掌握创建和使用触发器来实现数据的业务逻辑； 3掌握创建和使用触发器来实现数据的保护；,二、教学任务,1 介绍触发器的概念； 2 介绍触发器的触发机制； 3创建带有提示信息的触发器； 4创建限制取值范围约束的触发器； 5创建实现用户逻辑上数据完整性触发器； 6 分析设计分销系统中需要的触发器,6.1.1 触发器的概念,触发器是一种特殊的存储过程，其特殊性在于它不需要由用户调用执行，而是当用户对表中的数据
2、进行UPDATE、INSERT或DELETE操作时自动触发执行。触发器通常用于保证业务规则和数据完整性约束，其优点是用户可以用编程的方法来实现复杂的处理逻辑和业务规则，增强了数据完整性约束的功能。在SQL Server 2005中，触发器有了更进一步的功能，在数据表（库）发生Create、Alter和Drop操作时，也会自动激活执行。,6.1.2 触发器的分类,在SQL Server 2005中，触发器可以分为两大类：DML触发器和DDL触发器。 DML触发器：DML触发器是当数据库服务器中发生数据操作语言（Data Manipulation Language）事件时执行的存储过程。DML 事
3、件包括在指定表或视图中修改数据的 INSERT 语句、UPDATE 语句或 DELETE 语句。DML触发器又分为两类：After触发器和Instead Of触发器。DML 触发器可以查询其他表，还可以包含复杂的 T-SQL 语句。系统将触发器和触发它的语句作为可在触发器内回滚的单个事务对待，如果检测到错误（例如，磁盘空间不足），则整个事务即自动回滚。 DDL触发器：DDL触发器是当数据库服务器中发生数据定义语言（Data Definition Language）事件时执行的存储过程。这是SQL Server 2005 的新增功能。它与DML触发器不同的是，它不会为响应针对表或视图的UPDAT
4、E、INSERT或DELETE语句而激发，相反，它会为响应多种数据定义语言（DDL）语句而激发。这些语句主要是以CREATE、ALTER和DROP开头的语句。DDL触发器一般用于执行数据库中管理任务。如审核和规范数据库操作、防止数据库表结构被修改等。 DML触发器的分类 SQL Server 2005的DML触发器分为两类： After触发器和Instead Of触发器。 After触发器：这类触发器是在记录已经改变完之后（after），才会被激活执行，它主要是用于记录变更后的处理或检查，一旦发现错误，也可以用Rollback Transaction语句来回滚本次的操作。 Instead Of
5、触发器：这类触发器一般是用来取代原本的操作，在记录变更之前发生的，它并不去执行原来SQL语句里的操作（Insert、Update、Delete），而去执行触发器本身所定义的操作。,6.1.2 触发器的分类,DML触发器的工作原理 在DML触发器的工作过程中，SQL Server建立和管理两个临时的虚拟表，一个是Inserted（插入）表，一个是Deleted（删除）表。这两个表是建在数据库服务器的内存中的，是由系统管理的逻辑表，而不是真正存储在数据库中的物理表。这两个特殊表可供用户读取，但是用户不能直接修改表中的数据。 Inserted和Deleted两个表的结构与触发器所在数据表的结构是完全
6、一致的，当触发器的工作完成之后，这两个表也将会从内存中删除。,6.1.2 触发器的分类,6.1.3 触发器的创建,CREATE TRIGGER trigger_name ON table_name WITH ENCRYPTION FOR | AFTER | INSTEAD OF INSERT , DELETE , UPDATE AS SQL_statement, n,6.1.3 触发器的创建,参数含义说明如下： CREATE TRIGGER：用来触发器。 trigger_name：触发器名称，触发器是对象，必须具有数据库中的唯一名称。 ON table_name：用于指定触发执行触发器的表。
7、With Encryption：用来加密触发器。如果使用了这个参数，该触发器将会被加密，任何人都看不到触发器的内容了。 AFTER：指定触发器只有在引发触发器执行的SQL语句指定的操作都已经成功执行，并且所有的约束检查也成功完成后，才执行此触发器。这种类型的触发器称为后触发型触发器。 FOR：如果只是指定FOR关键字，则AFTER为默认值。 INSTEAD OF：指定执行触发器而不是执行引发触发器执行的SQL语句，从而替代触发语句的操作。这种触发器称为前触发型触发器，一个表只能定义一个INSTEAD OF触发器。 INSERT, DELETE, UPDATE：指定引发触发器执行的操作，若同时指
8、定多个操作，则各操作之间用逗号分隔。,6.1.4 触发器的实例,任务6-2：创建带有提示信息的触发器。当用户在期初库存表中插入数据时，产生一条提示信息。 在SQLQuery窗口中执行如下命令： Create TRIGGER TRI_insert_qckc on 期初库存 FOR INSERT AS PRINT 在期初库存表中插入了数据！,6.1.4 触发器的实例,任务6-3：创建限制取值范围约束的触发器。限制期初库存表中的期初数量和期初单价必须是大于零的数字。 在SQLQuery窗口中执行如下命令： Create TRIGGER TRI_check_qckc ON 期初库存 FOR INSER
9、T,UPDATE AS If exists(select * from inserted where isnull(期初数量,0)0 or isnull(期初单价,0)0) Begin PRINT 期初数量或期初单价不能为负数，更改失败！ Rollback End,6.1.4 触发器的实例,任务6-4：为收款单创建一个instead of 触发器，使得新插入记录中收款金额小于应收总额时，备注内容自动填写为“未收讫”，而收款金额等于应收总额时，备注内容自动填写为“已收讫”。 在SQLQuery窗口中执行如下命令： Create TRIGGER TRI_SKD_BZ ON 收款单 INSTEAD
10、OF INSERT AS begin insert into 收款单 select * from inserted update 收款单 set 备注=未收讫 where 收款金额应收总额 and (收款单号 in (select 收款单号 from inserted ) update 收款单 set 备注=已收讫 where 收款金额=应收总额 and (收款单号 in (select 收款单号 from inserted ) end,6.1.5 查看、修改和删除触发器,ALTER TRIGGER trigger_name ON table_name WITH ENCRYPTION FOR
11、| AFTER | INSTEAD OF INSERT , DELETE , UPDATE AS SQL_statement, n,6.1.5 查看、修改和删除触发器,任务6-8：修改触发器TRI_check_cgmx的定义，将采购订单明细表的数量字段的上限修改为1200。 在SQLQuery窗口中执行如下命令： ALTER TRIGGER TRI_check_cgmx ON 采购订单明细表 FOR INSERT,UPDATE AS If (select 数量 from inserted)1200 Begin PRINT 采购数量超出上限，操作失败！ Rollback end,6.1.5 查看
12、、修改和删除触发器,可以使用Drop Trigger语句来删除触发器。 任务6-9：删除触发器TRI_insert_qckc。 在SQLQuery窗口中执行如下命令： drop trigger TRI_insert_qckc 该语句执行后，触发器TRI_insert_qckc即被删除。,6.1.6 DDL触发器,创建DDL触发器的语法如下： CREATE TRIGGER trigger_name ON ALL SERVER | DATABASE WITH ,.n FOR | AFTER event_type | event_group ,.n AS sql_statement ; .n | E
13、XTERNAL NAME ; 参数含义说明如下： trigger_name：触发器名称，必须遵循标识符规则。 ON All Server：是将DDL触发器作用到整个当前的服务器上。如果指定了这个参数，在当前服务器上的任何一个数据库都能激活该触发器。 ON Database：是将DDL触发器作用到当前数据库，只能在这个数据库上激活该触发器。 For或After：是同一个意思，指定的是After触发器，DDL触发器无法作为Instead Of触发器。 event_type：执行之后将导致激发DDL触发器的Transact-SQL语言事件的名称。 event_group：预定义的Transact-S
14、QL语言事件分组的名称。,6.1.6 DDL触发器,任务6-10：创建用于保护“分销系统”数据库中的数据表不被删除的触发发器。 在SQLQuery窗口中执行如下命令： create trigger disable_table_dropping on database for drop_table as begin raiserror(分销系统数据表不能被删除,16,10) rollback end,6.2 分销系统触发器的设计,6.2.1 分销系统触发器规划,在分销系统的数据表中，销售订单明细表有字段数量、单价、金额。根据业务逻辑，金额单价数量，所以对于销售订单明细表应该创建一个触发器，使得金
15、额的值是由单价和数量的乘积得来的，保证该业务逻辑的准确性。同样地，根据业务逻辑，在某个销售订单中，销售订单明细表中字段金额和销售订单中字段总金额存在这样的关系：总金额等于该销售订单明细中的金额的总和。为此，还需要为销售订单明细表创建一个触发器来保证该业务逻辑。 类似地，在采购订单与采购订单明细表之间、盘点表与盘点表明细表之间、入库单与入库单明细表之间、出库单与出库单明细表之间、期初库存，也有相同的业务逻辑，都需要建立保证相关业务逻辑准确性的触发器。 在部分缺少外键约束的数据表中，比如入库单明细表，该表的仓库编码字段的值必须是仓库资料表中存在的仓库编码，因为仓库资料表中不存在的仓库编码就意味着这
16、样的仓库编码所标识的仓库是不存在的，现实中是绝不可能把商品入库到一个不存在的仓库中的。同样地，该表的仓位编码字段的值必须是仓位资料表中存在的数据。要保证上述数据完整性要求，可以创建相应的触发器。 在分销系统的数据表中，虽然不少表都定义了外键约束，但并没有定义级联删除和级联修改，为此，可以定义相应的触发器来达到相应的效果。比如，若某个销售订单被删除，则该销售订单对应的明细记录也能被自动删除。,6.2.2 分销系统触发器设计,任务6-11：为表期初库存创建一个触发器，保证插入新记录、更改了期初数量或更改了期初单价后期初金额都会随着变化而自动更新为期初数量和期初单价的乘积。 对此触发器可以做如下分析：如果【期初单价】或者【期初数量 】字段有更改，则将期初库存中序号跟inserted表中序号对应的记录的期初金额按公式【期
