第10章销售管理数据库中触发器的应用
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
CREATE TRIGGER 触发器名 ON { ALL SERVER | DATABASE } [WITH ENCRYPTION ] { FOR | AFTER } { DDL事件} [ ,...n ] AS
sql_statement 参数说明： 1. DATABASE指将DDL触发器的作用域应用于当前数据库。 2.ALL SERVER指将DDL触发器的作用域应用于当前服务器。 3.DDL事件包括 drop_table,alter_table,create_table,drop_database,alte r_database等。
SQL Server 2005数据库应用技术 清华大学出版社
创建DML触发器（续）
3、UPDATE触发器 【例1】 创建一个修改触发器，防止用户修改employee表的员工
的部门编号 create trigger 修改员工信息 on 员工 after update as if update(部门编号) begin print '不能更改员工的部门编号' rollback end
create trigger 删除员工 on 员工
after delete
as if( select 部门名称 from 部门 join deleted on 部门.部门编号=deleted.部门编 号)='人事部'
begin print '此为人事部门的员工，不能 删除记录'
rollback
print '此为人事部的员工，不能删除记录'end
10.2 DML触发器
10.2.1 DML触发器的类型 根据触发的时机可以把DML触发器划分为： After触发器：这类触发器是在记录已经改变完之
后（after），才会被激活执行，它主要是用于记 录变更后的处理或检查，一旦发现错误，也可以用 Rollback Transaction语句来回滚本次的操作。 Instead Of触发器：这类触发器一般是用来取代原 本要进行的操作，在记录变更之前发生的，它并不 去执行原来SQL语句里的操作（Insert、Update、 Delete），而去执行触发器本身所定义的操作。
from 部门 join deleted
on 部门.部门编号=deleted. 部门编号)<>’人事部'
delete 员工Байду номын сангаас
from 员工join deleted
on 员工.员工编号=deleted. 员工编号
else
p不r能in删t 除'此记为录人'事部的员工，
SQL Server 2005数据库应用技术 清华大学出版社
create trigger 销售 on 销售订单 after insert as update 商品 set 库存数量=库存数量-订购数量, 已售数量=已售数量+订购数量 from 商品 join inserted on 商品.商品编号=inserted.商品编号
create trigger 删除员工
on 员工
instead of delete
as
declare @部门名称 varchar(30)
select @部门名称=部门名称
from 部门join deleted
on 部门.部门编号=deleted. 部门编号
if(@部门名称!='人事部')
SQL Server 2005数据库应用技术 清华大学出版社
DDL触发器 DDL 触发器常用于如下的情况。
防止对数据库架构进行某些更改。 以响应数据库架构中的更改。 记录数据库架构中的更改或事件
SQL Server 2005数据库应用技术 清华大学出版社
DDL触发器
创建DDL触发器的语法格式如下。
SQL Server 2005数据库应用技术 清华大学出版社
INSERTED表和DELETED表
操作类型 INSERT DELETE UPDATE
INSERTED表 插入的记录 不创建 修改后的记录
DELETED表 不创建 删除的记录 修改前的记录
SQL Server 2005数据库应用技术 清华大学出版社
Inserted表和deleted表，由系统来维护，用户 不能对其修改，它们只存在内存而不是数据库中。 这两个表的结构总是与被触发器作用的表的结构 相同。触发器执行完之后，与该触发器相关联的 这两个表也会被删除。
SQL Server 2005数据库应用技术 清华大学出版社
Inserted逻辑表：存放用于执行insert和update语句 而要从表中插入的所有行。在执行insert或update 语 句时，新的行同时添加到激活触发器的表和inserted 表中，inserted表的内容是激活触发器的表中新行的 拷贝。 Deleted表中：存放用于执行delete或update语句而要 从表中删除的行。在执行delete或update语句时，被 删除的行从激活触发器的表中被移动到deleted表中， 两个表不会有共同的行。
SQL Server 2005数据库应用技术 清华大学出版社
创建DML触发器
2、DELETE触发器
【例1】在employee表上，创建一个触发器，其功能为：当对employee表进行 删除操作时，首先检查要删除的员工是否为人事部门的员工，如果不是的 话，可以删除该员工的消息；否则撤销此删除，并显示无法删除的信息。
创建DML触发器
1、INSERT触发器
【例1】创建名为reminder的触发器，当用户向部门 department表中插入一条部门记录时，向客户端发送一条提 示消息“插入一条记录！”。
create trigger remider on 部门 after insert as print '插入一条记录‘
第十章 销售管理数据库中触 发器的应用
SQL Server 2005数据库应用技术 清华大学出版社
技能目标
理解触发器的概念、功能和类型； 学会根据实际开发销售管理数据库中的触发器，以完
成系统整体设计的目的。
SQL Server 2005数据库应用技术 清华大学出版社
10.1.1 触发器
触发器实际上就是一种特殊类型的存储过程，它 在执行一定操作时自动触发执行。在SQL Server 2005之前的版本中，触发器是针对数据表的特殊的存 储过程，当这个表发生了Insert、Update或Delete操 作时，如果该表有对应操作的触发器，这个触发器就 会自动激活执行。在SQL Server 2005中，触发器有 了更进一步的功能，在数据表（库）发生Create、 Alter和Drop操作时，也会自动激活执行。
2、DELETE触发器
[例2]创建一个触发器,用于实现如下功能:当从部门表中删除 记录时,不允许删除表中的数据,并给出信息提示。
create trigger 删除部门 on 部门 instead of delete as print '不能删除部门表中的记录‘ 或:create trigger 删除部门 on 部门 after delete as begin rollback print '不能删除部门表中的记录' end
验证触发器： insert into 部门 values(5,'财务部','李亮','主管公司财务信息')
SQL Server 2005数据库应用技术 清华大学出版社
1、INSERT触发器
【例2】创建一个触发器实现当向销售订单表添加一 条记录时，自动修改商品表的库存数量和已售数量。
SQL Server 2005数据库应用技术 清华大学出版社
DDL触发器
【例2】建立用于保护指定的数据库服务器中的数据 库不被删除的触发器。
create trigger 不能删除数据库 on all server after drop_database as print '禁止删除当前服务器里的所有数据库' rollback
SQL Server 2005数据库应用技术 清华大学出版社
DDL触发器
【例1】创建一个触发器用于防止用户删除或更改销 售管理数据库中的任一数据表。
create trigger 不能删改表 on database for drop_table,alter_table as print '禁止删除或修改数据表' rollback
SQL Server 2005数据库应用技术 清华大学出版社
修改触发器
使用Management Studio 使用ALTER TRIGGER语句
语法格式如下。 ALTER TRIGGER 要修改的触发器名
ON 表名或视图名 [ WITH ENCRYPTION ] ( FOR | AFTER | INSTEAD OF ) { [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] } AS sql_statements
rollback
end
SQL Server 2005数据库应用技术 清华大学出版社
2、DELETE触发器 【例1】在employee表上，创建一个触发器，其功能为：当对employee表进行 删除操作时，首先检查要删除的员工是否为人事部门的员工，如果不是的话， 可以删除该员工的消息；否则撤销此删除，并显示无法删除的信息。
10.1.3触发器的类型
DML触发器 DML触发器是当数据库服务器中发生数据操作语
言（Data Manipulation Language）事件时执行 的存储过程
DDL触发器
DDL触发器是在响应数据定义语言（Data Definition Language）事件时执行的存储过程。
SQL Server 2005数据库应用技术 清华大学出版社
SQL Server 2005数据库应用技术 清华大学出版社
10.1.2 触发器的功能
完成更复杂的数据约束 检查所做的SQL所作的操作是否允许 修改其它数据表里的数据 调用更多的存储过程 返回自定义的错误信息 更改原本要操作的SQL语句 防止数据表结构被更改或数据表被删除
SQL Server 2005数据库应用技术 清华大学出版社
delete 员工
from 员工join deleted
on 员工.员工编号=deleted. 员工编号
else
print '此为人事部的员工， 不能删除记录'
create trigger 删除员工
on 员工
instead of delete
as
if( select 部门名称
SQL Server 2005数据库应用技术 清华大学出版社
触发器的类型
根据引发触发器的操作不同分为INSERT触发器、 UPDATE触发器和DELETE 触发器。
SQL Server 2005数据库应用技术 清华大学出版社
DML触发器
创建触发器的语法格式如下。
CREATE TRIGGER 触发器名 ON 表名或视图名 {FOR | AFTER | INSTEAD OF } {INSERT[，] | UPDAT E[，] | DELETE }
create trigger 删除员工 on 员工 after delete as declare @部门名称 varchar(30) select @部门名称=部门名称 from 部门 join deleted on 部门.部门编号=deleted.部门编号 if(@部门名称='人事部') begin
[WITH ENCRYPTION ] AS [IF UPDATE (列名1)
[{AND | OR } UPDATE(列名2)[…n] sql_statements
SQL Server 2005数据库应用技术 清华大学出版社
10.2.2 DML触发器的工作原理
在SQL Server 2005 里，执行触发器时，系统会 自动创建两个特殊的逻辑表，一个是Inserted表， 一个是Deleted表。
sql_statement 参数说明： 1. DATABASE指将DDL触发器的作用域应用于当前数据库。 2.ALL SERVER指将DDL触发器的作用域应用于当前服务器。 3.DDL事件包括 drop_table,alter_table,create_table,drop_database,alte r_database等。
SQL Server 2005数据库应用技术 清华大学出版社
创建DML触发器（续）
3、UPDATE触发器 【例1】 创建一个修改触发器，防止用户修改employee表的员工
的部门编号 create trigger 修改员工信息 on 员工 after update as if update(部门编号) begin print '不能更改员工的部门编号' rollback end
create trigger 删除员工 on 员工
after delete
as if( select 部门名称 from 部门 join deleted on 部门.部门编号=deleted.部门编 号)='人事部'
begin print '此为人事部门的员工，不能 删除记录'
rollback
print '此为人事部的员工，不能删除记录'end
10.2 DML触发器
10.2.1 DML触发器的类型 根据触发的时机可以把DML触发器划分为： After触发器：这类触发器是在记录已经改变完之
后（after），才会被激活执行，它主要是用于记 录变更后的处理或检查，一旦发现错误，也可以用 Rollback Transaction语句来回滚本次的操作。 Instead Of触发器：这类触发器一般是用来取代原 本要进行的操作，在记录变更之前发生的，它并不 去执行原来SQL语句里的操作（Insert、Update、 Delete），而去执行触发器本身所定义的操作。
from 部门 join deleted
on 部门.部门编号=deleted. 部门编号)<>’人事部'
delete 员工Байду номын сангаас
from 员工join deleted
on 员工.员工编号=deleted. 员工编号
else
p不r能in删t 除'此记为录人'事部的员工，
SQL Server 2005数据库应用技术 清华大学出版社
create trigger 销售 on 销售订单 after insert as update 商品 set 库存数量=库存数量-订购数量, 已售数量=已售数量+订购数量 from 商品 join inserted on 商品.商品编号=inserted.商品编号
create trigger 删除员工
on 员工
instead of delete
as
declare @部门名称 varchar(30)
select @部门名称=部门名称
from 部门join deleted
on 部门.部门编号=deleted. 部门编号
if(@部门名称!='人事部')
SQL Server 2005数据库应用技术 清华大学出版社
DDL触发器 DDL 触发器常用于如下的情况。
防止对数据库架构进行某些更改。 以响应数据库架构中的更改。 记录数据库架构中的更改或事件
SQL Server 2005数据库应用技术 清华大学出版社
DDL触发器
创建DDL触发器的语法格式如下。
SQL Server 2005数据库应用技术 清华大学出版社
INSERTED表和DELETED表
操作类型 INSERT DELETE UPDATE
INSERTED表 插入的记录 不创建 修改后的记录
DELETED表 不创建 删除的记录 修改前的记录
SQL Server 2005数据库应用技术 清华大学出版社
Inserted表和deleted表，由系统来维护，用户 不能对其修改，它们只存在内存而不是数据库中。 这两个表的结构总是与被触发器作用的表的结构 相同。触发器执行完之后，与该触发器相关联的 这两个表也会被删除。
SQL Server 2005数据库应用技术 清华大学出版社
Inserted逻辑表：存放用于执行insert和update语句 而要从表中插入的所有行。在执行insert或update 语 句时，新的行同时添加到激活触发器的表和inserted 表中，inserted表的内容是激活触发器的表中新行的 拷贝。 Deleted表中：存放用于执行delete或update语句而要 从表中删除的行。在执行delete或update语句时，被 删除的行从激活触发器的表中被移动到deleted表中， 两个表不会有共同的行。
SQL Server 2005数据库应用技术 清华大学出版社
创建DML触发器
2、DELETE触发器
【例1】在employee表上，创建一个触发器，其功能为：当对employee表进行 删除操作时，首先检查要删除的员工是否为人事部门的员工，如果不是的 话，可以删除该员工的消息；否则撤销此删除，并显示无法删除的信息。
创建DML触发器
1、INSERT触发器
【例1】创建名为reminder的触发器，当用户向部门 department表中插入一条部门记录时，向客户端发送一条提 示消息“插入一条记录！”。
create trigger remider on 部门 after insert as print '插入一条记录‘
第十章 销售管理数据库中触 发器的应用
SQL Server 2005数据库应用技术 清华大学出版社
技能目标
理解触发器的概念、功能和类型； 学会根据实际开发销售管理数据库中的触发器，以完
成系统整体设计的目的。
SQL Server 2005数据库应用技术 清华大学出版社
10.1.1 触发器
触发器实际上就是一种特殊类型的存储过程，它 在执行一定操作时自动触发执行。在SQL Server 2005之前的版本中，触发器是针对数据表的特殊的存 储过程，当这个表发生了Insert、Update或Delete操 作时，如果该表有对应操作的触发器，这个触发器就 会自动激活执行。在SQL Server 2005中，触发器有 了更进一步的功能，在数据表（库）发生Create、 Alter和Drop操作时，也会自动激活执行。
2、DELETE触发器
[例2]创建一个触发器,用于实现如下功能:当从部门表中删除 记录时,不允许删除表中的数据,并给出信息提示。
create trigger 删除部门 on 部门 instead of delete as print '不能删除部门表中的记录‘ 或:create trigger 删除部门 on 部门 after delete as begin rollback print '不能删除部门表中的记录' end
验证触发器： insert into 部门 values(5,'财务部','李亮','主管公司财务信息')
SQL Server 2005数据库应用技术 清华大学出版社
1、INSERT触发器
【例2】创建一个触发器实现当向销售订单表添加一 条记录时，自动修改商品表的库存数量和已售数量。
SQL Server 2005数据库应用技术 清华大学出版社
DDL触发器
【例2】建立用于保护指定的数据库服务器中的数据 库不被删除的触发器。
create trigger 不能删除数据库 on all server after drop_database as print '禁止删除当前服务器里的所有数据库' rollback
SQL Server 2005数据库应用技术 清华大学出版社
DDL触发器
【例1】创建一个触发器用于防止用户删除或更改销 售管理数据库中的任一数据表。
create trigger 不能删改表 on database for drop_table,alter_table as print '禁止删除或修改数据表' rollback
SQL Server 2005数据库应用技术 清华大学出版社
修改触发器
使用Management Studio 使用ALTER TRIGGER语句
语法格式如下。 ALTER TRIGGER 要修改的触发器名
ON 表名或视图名 [ WITH ENCRYPTION ] ( FOR | AFTER | INSTEAD OF ) { [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] } AS sql_statements
rollback
end
SQL Server 2005数据库应用技术 清华大学出版社
2、DELETE触发器 【例1】在employee表上，创建一个触发器，其功能为：当对employee表进行 删除操作时，首先检查要删除的员工是否为人事部门的员工，如果不是的话， 可以删除该员工的消息；否则撤销此删除，并显示无法删除的信息。
10.1.3触发器的类型
DML触发器 DML触发器是当数据库服务器中发生数据操作语
言（Data Manipulation Language）事件时执行 的存储过程
DDL触发器
DDL触发器是在响应数据定义语言（Data Definition Language）事件时执行的存储过程。
SQL Server 2005数据库应用技术 清华大学出版社
SQL Server 2005数据库应用技术 清华大学出版社
10.1.2 触发器的功能
完成更复杂的数据约束 检查所做的SQL所作的操作是否允许 修改其它数据表里的数据 调用更多的存储过程 返回自定义的错误信息 更改原本要操作的SQL语句 防止数据表结构被更改或数据表被删除
SQL Server 2005数据库应用技术 清华大学出版社
delete 员工
from 员工join deleted
on 员工.员工编号=deleted. 员工编号
else
print '此为人事部的员工， 不能删除记录'
create trigger 删除员工
on 员工
instead of delete
as
if( select 部门名称
SQL Server 2005数据库应用技术 清华大学出版社
触发器的类型
根据引发触发器的操作不同分为INSERT触发器、 UPDATE触发器和DELETE 触发器。
SQL Server 2005数据库应用技术 清华大学出版社
DML触发器
创建触发器的语法格式如下。
CREATE TRIGGER 触发器名 ON 表名或视图名 {FOR | AFTER | INSTEAD OF } {INSERT[，] | UPDAT E[，] | DELETE }
create trigger 删除员工 on 员工 after delete as declare @部门名称 varchar(30) select @部门名称=部门名称 from 部门 join deleted on 部门.部门编号=deleted.部门编号 if(@部门名称='人事部') begin
[WITH ENCRYPTION ] AS [IF UPDATE (列名1)
[{AND | OR } UPDATE(列名2)[…n] sql_statements
SQL Server 2005数据库应用技术 清华大学出版社
10.2.2 DML触发器的工作原理
在SQL Server 2005 里，执行触发器时，系统会 自动创建两个特殊的逻辑表，一个是Inserted表， 一个是Deleted表。