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

1、 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社第十章第十章 销售管理数据库中触销售管理数据库中触发器的应用发器的应用 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社技能目标技能目标u理解触发器的概念、功能和类型；理解触发器的概念、功能和类型；u学会根据实际开发销售管理数据库中的触发器，以完学会根据实际开发销售管理数据库中的触发器，以完成系统整体设计的目的。成系统整体设计的目的。 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社10.1.1 10.1.1 触发器触发器 触

2、发器实际上就是一种特殊类型的存储过程，它触发器实际上就是一种特殊类型的存储过程，它在执行一定操作时自动触发执行。在在执行一定操作时自动触发执行。在SQL Server SQL Server 20052005之前的版本中，触发器是针对数据表的特殊的存之前的版本中，触发器是针对数据表的特殊的存储过程，当这个表发生了储过程，当这个表发生了InsertInsert、UpdateUpdate或或DeleteDelete操操作时，如果该表有对应操作的触发器，这个触发器就作时，如果该表有对应操作的触发器，这个触发器就会自动激活执行。在会自动激活执行。在SQL Server 2005SQL Server 20

3、05中，触发器有中，触发器有了更进一步的功能，在数据表（库）发生了更进一步的功能，在数据表（库）发生CreateCreate、AlterAlter和和DropDrop操作时，也会自动激活执行。操作时，也会自动激活执行。 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社10.1.2 触发器的功能u完成更复杂的数据约束完成更复杂的数据约束 u检查所做的检查所做的SQL所作的操作是否允许所作的操作是否允许u修改其它数据表里的数据修改其它数据表里的数据 u调用更多的存储过程调用更多的存储过程 u返回自定义的错误信息返回自定义的错误信息 u更改原本要操作的更改原本

4、要操作的SQL语句语句 u防止数据表结构被更改或数据表被删除防止数据表结构被更改或数据表被删除 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社10.1.3触发器的类型uDML触发器 DMLDML触发器是当数据库服务器中发生数据操作语触发器是当数据库服务器中发生数据操作语言（言（Data Manipulation LanguageData Manipulation Language）事件时执行）事件时执行的存储过程的存储过程uDDL触发器 DDL触发器是在响应数据定义语言（触发器是在响应数据定义语言（Data Definition Language）事件

5、时执行的存储过程。）事件时执行的存储过程。 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社10.2 DML触发器 10.2.1 DML触发器的类型 根据触发的时机可以把根据触发的时机可以把DMLDML触发器划分为：触发器划分为：uAfterAfter触发器：这类触发器是在记录已经改变完之触发器：这类触发器是在记录已经改变完之后（后（afterafter），才会被激活执行，它主要是用于记），才会被激活执行，它主要是用于记录变更后的处理或检查，一旦发现错误，也可以用录变更后的处理或检查，一旦发现错误，也可以用Rollback TransactionRoll

6、back Transaction语句来回滚本次的操作。语句来回滚本次的操作。uInstead OfInstead Of触发器：这类触发器一般是用来取代原触发器：这类触发器一般是用来取代原本要进行的操作，在记录变更之前发生的，它并不本要进行的操作，在记录变更之前发生的，它并不去执行原来去执行原来SQLSQL语句里的操作（语句里的操作（InsertInsert、UpdateUpdate、DeleteDelete），而去执行触发器本身所定义的操作。），而去执行触发器本身所定义的操作。 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社触发器的类型触发器的类型u根

7、据引发触发器的操作不同分为根据引发触发器的操作不同分为INSERTINSERT触发器、触发器、UPDATEUPDATE触发器和触发器和DELETE DELETE 触发器。触发器。 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社DMLDML触发器触发器 创建触发器的语法格式如下。创建触发器的语法格式如下。CREATE TRIGGER 触发器名触发器名 ON 表名或视图名表名或视图名 FOR | AFTER | INSTEAD OF INSERT， | UPDAT E， | DELETE WITH ENCRYPTION AS IF UPDATE (列名列名

8、1) AND | OR UPDATE(列名列名2)n sql_statements SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社10.2.2 DML触发器的工作原理u在在SQL Server 2005 SQL Server 2005 里，执行触发器时，系统会里，执行触发器时，系统会自动创建两个特殊的逻辑表，一个是自动创建两个特殊的逻辑表，一个是InsertedInserted表，表，一个是一个是DeletedDeleted表。表。uInsertedInserted表和表和deleteddeleted表，由系统来维护，用户表，由系统来维护，用户不能对其

9、修改，它们只存在内存而不是数据库中。不能对其修改，它们只存在内存而不是数据库中。这两个表的结构总是与被触发器作用的表的结构这两个表的结构总是与被触发器作用的表的结构相同。触发器执行完之后，与该触发器相关联的相同。触发器执行完之后，与该触发器相关联的这两个表也会被删除。这两个表也会被删除。 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社uInsertedInserted逻辑表：存放用于执行逻辑表：存放用于执行insertinsert和和updateupdate语句语句而要从表中插入的所有行。在执行而要从表中插入的所有行。在执行insertinsert或或

10、update update 语语句时，新的行同时添加到激活触发器的表和句时，新的行同时添加到激活触发器的表和insertedinserted表中，表中，insertedinserted表的内容是激活触发器的表中新行的表的内容是激活触发器的表中新行的拷贝。拷贝。DeletedDeleted表中：存放用于执行表中：存放用于执行deletedelete或或updateupdate语句而要语句而要从表中删除的行。在执行从表中删除的行。在执行deletedelete或或updateupdate语句时，被语句时，被删除的行从激活触发器的表中被移动到删除的行从激活触发器的表中被移动到deleteddelet

11、ed表中，表中，两个表不会有共同的行。两个表不会有共同的行。 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社INSERTEDINSERTED表和表和DELETEDDELETED表表 操作类型操作类型INSERTEDINSERTED表表DELETEDDELETED表表INSERT插入的记录插入的记录不创建不创建DELETE不创建不创建删除的记录删除的记录UPDATE修改后的记录修改后的记录修改前的记录修改前的记录 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社创建创建DMLDML触发器触发器 1 1、INSERT

12、INSERT触发器触发器【例例1】创建名为创建名为reminder的触发器，当用户向部门的触发器，当用户向部门department表中插入一条部门记录时，向客户端发送一条提表中插入一条部门记录时，向客户端发送一条提示消息示消息“插入一条记录！插入一条记录！”。ucreate trigger remideruon 部门部门uafter insertuasuprint 插入一条记录插入一条记录u验证触发器：验证触发器：uinsert into 部门部门uvalues(5,财务部财务部,李亮李亮,主管公司财务信息主管公司财务信息) SQL Server 2005数据库应用技术数据库应用技术 清华大学

13、出版社清华大学出版社1 1、INSERTINSERT触发器触发器u【例例2 2】创建一个触发器实现当向销售订单表添加一创建一个触发器实现当向销售订单表添加一条记录时，自动修改商品表的库存数量和已售数量。条记录时，自动修改商品表的库存数量和已售数量。ucreate trigger create trigger 销售销售uon on 销售订单销售订单uafter insertafter insertuasasuupdate update 商品商品uset set 库存数量库存数量= =库存数量库存数量- -订购数量订购数量, ,u已售数量已售数量= =已售数量已售数量+ +订购数量订购数量ufro

14、m from 商品商品 join insertedjoin inserteduon on 商品商品. .商品编号商品编号=inserted.=inserted.商品编号商品编号 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社创建创建DMLDML触发器触发器2 2、DELETEDELETE触发器触发器【例例1 1】在在employeeemployee表上，创建一个触发器，其功能为：当对表上，创建一个触发器，其功能为：当对employeeemployee表进行表进行删除操作时，首先检查要删除的员工是否为人事部门的员工，如果不是的删除操作时，首先检查要删除的

15、员工是否为人事部门的员工，如果不是的话，可以删除该员工的消息；否则撤销此删除，并显示无法删除的信息。话，可以删除该员工的消息；否则撤销此删除，并显示无法删除的信息。 ucreate trigger create trigger 删除员工删除员工uon on 员工员工uafter deleteafter deleteuasasudeclare declare 部门名称部门名称 varchar(30)varchar(30)uselect select 部门名称部门名称= =部门名称部门名称ufrom from 部门部门 join deletedjoin deleteduon on 部门部门. .部

16、门编号部门编号=deleted.=deleted.部门编号部门编号uif(if(部门名称部门名称=人事部人事部)ubeginbeginuprint print 此为人事部的员工，不能删除记录此为人事部的员工，不能删除记录 urollbackrollbackuendendcreate trigger 删除员工删除员工on 员工员工after deleteasif( select 部门名称部门名称from 部门部门 join deletedon 部门部门.部门编号部门编号=deleted.部门编部门编号号)=人事部人事部beginprint 此为人事部门的员工，不能此为人事部门的员工，不能删除记录

17、删除记录rollbackend2 2、DELETEDELETE触发器触发器【例例1 1】在在employeeemployee表上，创建一个触发器，其功能为：当对表上，创建一个触发器，其功能为：当对employeeemployee表进行表进行删除操作时，首先检查要删除的员工是否为人事部门的员工，如果不是的话，删除操作时，首先检查要删除的员工是否为人事部门的员工，如果不是的话，可以删除该员工的消息；否则撤销此删除，并显示无法删除的信息。可以删除该员工的消息；否则撤销此删除，并显示无法删除的信息。 ucreatecreate triggertrigger 删除员工删除员工uonon 员工员工uins

18、teadinstead ofof deletedeleteuasasudeclaredeclare 部部门名称门名称 varcharvarchar( (3030) )uselectselect 部门名称部门名称= =部门名称部门名称ufromfrom 部门部门joinjoin deleted deleteduonon 部门部门. .部门编号部门编号= =deleteddeleted. .部门编号部门编号uifif( ( 部门名称部门名称!=!= 人事部人事部 ) )udeletedelete 员工员工ufromfrom 员工员工joinjoin deleted deleteduonon 员工员

19、工. .员工编号员工编号= =deleteddeleted. .员工编号员工编号uelseelseuprintprint 此为人事部的员工，此为人事部的员工，不能删除记录不能删除记录 ucreate trigger create trigger 删除员工删除员工uon on 员工员工uinstead of deleteinstead of deleteuasasuif( select if( select 部门名称部门名称ufrom from 部门部门 join deleted join deleteduon on 部门部门. .部门编号部门编号=deleted.=deleted.部门编号部门

20、编号)人事部人事部 udelete delete 员工员工ufrom from 员工员工join deletedjoin deleteduon on 员工员工. .员工编号员工编号=deleted.=deleted.员工编号员工编号uelseelseuprint print 此为人事部的员工，此为人事部的员工，不能删除记录不能删除记录 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社2 2、DELETEDELETE触发器触发器u 例例22创建一个触发器创建一个触发器

21、, ,用于实现如下功能用于实现如下功能: :当从部门表中删除当从部门表中删除记录时记录时, ,不允许删除表中的数据不允许删除表中的数据, ,并给出信息提示。并给出信息提示。ucreate trigger create trigger 删除部门删除部门uon on 部门部门uinstead of deleteinstead of deleteuasasuprint print 不能删除部门表中的记录不能删除部门表中的记录u或或: :create trigger create trigger 删除部门删除部门uon on 部门部门uafter deleteafter deleteuasasubeg

22、inbeginurollbackrollbackuprint print 不能删除部门表中的记录不能删除部门表中的记录 uendend SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社创建创建DMLDML触发器（续）触发器（续）3 3、UPDATEUPDATE触发器触发器【例例1 1】 创建一个修改触发器，防止用户修改创建一个修改触发器，防止用户修改employeeemployee表的员工表的员工的部门编号的部门编号 u create trigger create trigger 修改员工信息修改员工信息uon on 员工员工uafter updatea

23、fter updateuasasuif update(if update(部门编号部门编号) )ubeginbeginuprint print 不能更改员工的部门编号不能更改员工的部门编号 urollbackrollbackuendend SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社修改触发器修改触发器 u使用使用Management Studio Management Studio u使用使用ALTER TRIGGERALTER TRIGGER语句语句 语法格式如下。语法格式如下。ALTER TRIGGER 要修改的触发器名要修改的触发器名 ON

24、表名或视图名表名或视图名 WITH ENCRYPTION ( FOR | AFTER | INSTEAD OF ) DELETE , INSERT , UPDATE ASsql_statements SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社DDLDDL触发器触发器 DDL DDL 触发器常用于如下的情况。触发器常用于如下的情况。 u防止对数据库架构进行某些更改。防止对数据库架构进行某些更改。u以响应数据库架构中的更改。以响应数据库架构中的更改。 u记录数据库架构中的更改或事件记录数据库架构中的更改或事件 SQL Server 2005数据库应用技

25、术数据库应用技术 清华大学出版社清华大学出版社u创建创建DDLDDL触发器的语法格式如下。触发器的语法格式如下。 CREATE TRIGGER 触发器名触发器名 ON ALL SERVER | DATABASE WITH ENCRYPTION FOR | AFTER DDL事件事件 ,.n AS sql_statement参数说明：参数说明：1. DATABASE指将指将DDL触发器的作用域应用于当前数据库。触发器的作用域应用于当前数据库。 2.ALL SERVER2.ALL SERVER指将指将DDLDDL触发器的作用域应用于当前服务器。触发器的作用域应用于当前服务器。3.DDL3.DDL事

26、件包括事件包括drop_table,alter_table,create_table,drop_database,altedrop_table,alter_table,create_table,drop_database,alter_databaser_database等。等。DDLDDL触发器触发器 SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社DDLDDL触发器触发器u【例例1 1】创建一个触发器用于防止用户删除或更改销创建一个触发器用于防止用户删除或更改销售管理数据库中的任一数据表。售管理数据库中的任一数据表。ucreate trigger cr

27、eate trigger 不能删改表不能删改表uon databaseon databaseufor drop_table,alter_tablefor drop_table,alter_tableuasasuprint print 禁止删除或修改数据表禁止删除或修改数据表 urollbackrollback SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社DDLDDL触发器触发器u【例例2 2】建立用于保护指定的数据库服务器中的数据建立用于保护指定的数据库服务器中的数据库不被删除的触发器。库不被删除的触发器。ucreate trigger create

28、 trigger 不能删除数据库不能删除数据库uon all serveron all serveruafter drop_databaseafter drop_databaseuasasuprint print 禁止删除当前服务器里的所有数据库禁止删除当前服务器里的所有数据库 urollbackrollback SQL Server 2005数据库应用技术数据库应用技术 清华大学出版社清华大学出版社查看触发器查看触发器 使用系统存储过程使用系统存储过程sp_helpsp_help、sp_helptextsp_helptext和和sp_depentssp_depents分别查看触发器的不同信息。分别查看触发器的不同信息。usp_ help