第10章 销售管理数据库中触发器的应用.ppt

1、第十章 销售管理数据库中触发器的应用,SQL Server 2005数据库应用技术 清华大学出版社,技能目标,理解触发器的概念、功能和类型； 学会根据实际开发销售管理数据库中的触发器，以完成系统整体设计的目的。,SQL Server 2005数据库应用技术 清华大学出版社,10.1.1 触发器,触发器实际上就是一种特殊类型的存储过程，它在执行一定操作时自动触发执行。在SQL Server 2005之前的版本中，触发器是针对数据表的特殊的存储过程，当这个表发生了Insert、Update或Delete操作时，如果该表有对应操作的触发器，这个触发器就会自动激活执行。在SQL Server 2005

2、中，触发器有了更进一步的功能，在数据表（库）发生Create、Alter和Drop操作时，也会自动激活执行。,SQL Server 2005数据库应用技术 清华大学出版社,10.1.2 触发器的功能,完成更复杂的数据约束 检查所做的SQL所作的操作是否允许 修改其它数据表里的数据 调用更多的存储过程 返回自定义的错误信息 更改原本要操作的SQL语句 防止数据表结构被更改或数据表被删除,SQL Server 2005数据库应用技术 清华大学出版社,10.1.3触发器的类型,DML触发器 DML触发器是当数据库服务器中发生数据操作语言（Data Manipulation Language）事件时执

3、行的存储过程 DDL触发器 DDL触发器是在响应数据定义语言（Data Definition Language）事件时执行的存储过程。,SQL Server 2005数据库应用技术 清华大学出版社,10.2 DML触发器,10.2.1 DML触发器的类型 根据触发的时机可以把DML触发器划分为： After触发器：这类触发器是在记录已经改变完之后（after），才会被激活执行，它主要是用于记录变更后的处理或检查，一旦发现错误，也可以用Rollback Transaction语句来回滚本次的操作。 Instead Of触发器：这类触发器一般是用来取代原本要进行的操作，在记录变更之前发生的，它并不

4、去执行原来SQL语句里的操作（Insert、Update、Delete），而去执行触发器本身所定义的操作。,SQL Server 2005数据库应用技术 清华大学出版社,触发器的类型,根据引发触发器的操作不同分为INSERT触发器、UPDATE触发器和DELETE 触发器。,SQL Server 2005数据库应用技术 清华大学出版社,DML触发器,创建触发器的语法格式如下。 CREATE TRIGGER 触发器名 ON 表名或视图名 FOR | AFTER | INSTEAD OF INSERT， | UPDAT E， | DELETE WITH ENCRYPTION AS IF UPDAT

5、E (列名1) AND | OR UPDATE(列名2)n sql_statements,SQL Server 2005数据库应用技术 清华大学出版社,10.2.2 DML触发器的工作原理,在SQL Server 2005 里，执行触发器时，系统会自动创建两个特殊的逻辑表，一个是Inserted表，一个是Deleted表。 Inserted表和deleted表，由系统来维护，用户不能对其修改，它们只存在内存而不是数据库中。这两个表的结构总是与被触发器作用的表的结构相同。触发器执行完之后，与该触发器相关联的这两个表也会被删除。,SQL Server 2005数据库应用技术 清华大学出版社,Ins

6、erted逻辑表：存放用于执行insert和update语句而要从表中插入的所有行。在执行insert或update 语句时，新的行同时添加到激活触发器的表和inserted表中，inserted表的内容是激活触发器的表中新行的拷贝。Deleted表中：存放用于执行delete或update语句而要从表中删除的行。在执行delete或update语句时，被删除的行从激活触发器的表中被移动到deleted表中，两个表不会有共同的行。,SQL Server 2005数据库应用技术 清华大学出版社,INSERTED表和DELETED表,SQL Server 2005数据库应用技术 清华大学出版社,创

7、建DML触发器,1、INSERT触发器 【例1】创建名为reminder的触发器，当用户向部门department表中插入一条部门记录时，向客户端发送一条提示消息“插入一条记录！”。 create trigger remider on 部门 after insert as print 插入一条记录 验证触发器： insert into 部门 values(5,财务部,李亮,主管公司财务信息),SQL Server 2005数据库应用技术 清华大学出版社,1、INSERT触发器,【例2】创建一个触发器实现当向销售订单表添加一条记录时，自动修改商品表的库存数量和已售数量。 create trigg

8、er 销售 on 销售订单 after insert as update 商品 set 库存数量=库存数量-订购数量, 已售数量=已售数量+订购数量 from 商品 join inserted on 商品.商品编号=inserted.商品编号,SQL Server 2005数据库应用技术 清华大学出版社,创建DML触发器,2、DELETE触发器 【例1】在employee表上，创建一个触发器，其功能为：当对employee表进行删除操作时，首先检查要删除的员工是否为人事部门的员工，如果不是的话，可以删除该员工的消息；否则撤销此删除，并显示无法删除的信息。 create trigger 删除员工

9、 on 员工 after delete as declare 部门名称 varchar(30) select 部门名称=部门名称 from 部门 join deleted on 部门.部门编号=deleted.部门编号 if(部门名称=人事部) begin print 此为人事部的员工，不能删除记录 rollback end,create trigger 删除员工 on 员工 after delete as if( select 部门名称 from 部门 join deleted on 部门.部门编号=deleted.部门编号)=人事部 begin print 此为人事部门的员工，不能删除记录

10、 rollback end,2、DELETE触发器【例1】在employee表上，创建一个触发器，其功能为：当对employee表进行删除操作时，首先检查要删除的员工是否为人事部门的员工，如果不是的话，可以删除该员工的消息；否则撤销此删除，并显示无法删除的信息。,create trigger 删除员工 on 员工 instead of delete as declare 部门名称 varchar(30) select 部门名称=部门名称 from 部门join deleted on 部门.部门编号=deleted.部门编号 if(部门名称!=人事部) delete 员工 from 员工join

11、 deleted on 员工.员工编号=deleted.员工编号 else print 此为人事部的员工，不能删除记录,create trigger 删除员工 on 员工 instead of delete as if( select 部门名称 from 部门 join deleted on 部门.部门编号=deleted.部门编号)人事部 delete 员工 from 员工join deleted on 员工.员工编号=deleted.员工编号 else print 此为人事部的员工，不能删除记录,SQL Server 2005数据库应用技术 清华大学出版社,SQL Server 2005数

12、据库应用技术 清华大学出版社,2、DELETE触发器,例2创建一个触发器,用于实现如下功能:当从部门表中删除记录时,不允许删除表中的数据,并给出信息提示。 create trigger 删除部门 on 部门 instead of delete as print 不能删除部门表中的记录 或:create trigger 删除部门 on 部门 after delete as begin rollback print 不能删除部门表中的记录 end,SQL Server 2005数据库应用技术 清华大学出版社,创建DML触发器（续）,3、UPDATE触发器 【例1】 创建一个修改触发器，防止用户修改

13、employee表的员工的部门编号 create trigger 修改员工信息 on 员工 after update as if update(部门编号) begin print 不能更改员工的部门编号 rollback end,SQL Server 2005数据库应用技术 清华大学出版社,修改触发器,使用Management Studio 使用ALTER TRIGGER语句 语法格式如下。 ALTER TRIGGER 要修改的触发器名 ON 表名或视图名 WITH ENCRYPTION ( FOR | AFTER | INSTEAD OF ) DELETE , INSERT , UPDATE

14、 AS sql_statements,SQL Server 2005数据库应用技术 清华大学出版社,DDL触发器,DDL 触发器常用于如下的情况。 防止对数据库架构进行某些更改。 以响应数据库架构中的更改。 记录数据库架构中的更改或事件,SQL Server 2005数据库应用技术 清华大学出版社,创建DDL触发器的语法格式如下。 CREATE TRIGGER 触发器名 ON ALL SERVER | DATABASE WITH ENCRYPTION FOR | AFTER DDL事件 ,.n AS sql_statement 参数说明： 1. DATABASE指将DDL触发器的作用域应用于当

15、前数据库。 2.ALL SERVER指将DDL触发器的作用域应用于当前服务器。 3.DDL事件包括drop_table,alter_table,create_table,drop_database,alter_database等。,DDL触发器,SQL Server 2005数据库应用技术 清华大学出版社,DDL触发器,【例1】创建一个触发器用于防止用户删除或更改销售管理数据库中的任一数据表。 create trigger 不能删改表 on database for drop_table,alter_table as print 禁止删除或修改数据表 rollback,SQL Server 2

16、005数据库应用技术 清华大学出版社,DDL触发器,【例2】建立用于保护指定的数据库服务器中的数据库不被删除的触发器。 create trigger 不能删除数据库 on all server after drop_database as print 禁止删除当前服务器里的所有数据库 rollback,SQL Server 2005数据库应用技术 清华大学出版社,查看触发器,使用系统存储过程sp_help、sp_helptext和sp_depents分别查看触发器的不同信息。 sp_ help ：显示触发器的所有者和创建时间 sp_ helptext：显示触发器的源代码 sp_depends：

17、显示该触发器参考的对象清单,SQL Server 2005数据库应用技术 清华大学出版社,删除触发器,使用Management Studio 使用DROP TRIGGER命令。 语法格式如下。 DROP TRIGGER trigger_name ,.n ON DATABASE | ALL SERVER ; 【例】删除department_undelete触发器。,SQL Server 2005数据库应用技术 清华大学出版社,禁止或启用触发器,禁用触发器 当不再需要某个触发器时，可将其禁用或删除。语法格式如下。 DISABLE TRIGGER ALL| 触发器名,.n ON object_name