存储过程与触发器补充

sp_depends：用于查看指定触发器所引用的表或者指定的表涉及
2. 修改触发器
（１）使用企业管理器修改触发器正文 （２）使用sp_rename命令修改触发器的名称 sp_rename oldname,newname
3. 使用alter trigger命令修改触发器正文
语法形式如下： ALTER TRIGGER trigger_name ON(table|view) [WITHENCRYPTION] { { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [DELETE] } [NOT FOR REPLICATION] AS [if update(列名) [and | or update(列名)]] sql_statement
【例13】对于XSCJ数据库，如果在XS表中添加或更改数 据，则将向客户端显示一条信息。 /*使用带有提示消息的触发器*/
USE XSCJ IF EXISTS (SELECT name FROM sysobjects WHERE name = 'reminder' AND type = 'TR') DROP TRIGGER reminder GO CREATE TRIGGER reminder ON XS FOR INSERT, UPDATE AS RAISERROR (4008, 16, 10)
CREATE TRIGGER tri_ins_产品销售 ON 产品销售 FOR INSERT AS IF (SELECT COUNT(*) FROM 产品 a, inserted i WHERE a.产品编号=i.产品编号) =0 BEGIN RAISERROR(‘不能插入不存在的产品号！',16,1) ROLLBACK TRANSACTION END ELSE IF (SELECT 销售日期 FROM inserted i) > getdate() BEGIN RAISERROR('不能插入迟于今天的销售记录！',16,1) ROLLBACK TRANSACTION END
触发器中使用的特殊表
inserted 逻辑表：当向表中插入数据时，INSERT触发 器触发执行，新的记录插入到触发器表和inserted表中。 deleted 逻辑表：用于保存已从表中删除的记录，当触 发一个DELETE触发器时，被删除的记录存放到deleted 逻辑表中。 修改记录相当于删除原有记录，插入一条新记录。因此 表中原记录存放到deleted表中，而新记录存放在inserted 表中。 触发器中的SQL语句涉及的数据均来自上述两张逻辑表 中。这两张表结构上类似于定义触发器的表
3. 使用DELETE触发器 • DELETE触发器对指定表执行删除操作时激活。 • 指定表中被删除的元组放入deleted逻辑表中，触发器 检查该表的数据确定下一步的处理。 • 通常用于两种情况： 1）防止那些确实需要删除但会引起数据一致性问题的 记录的删除。 2）执行可删除主记录的子记录的级联删除操作。
【例15】 删除触发器reminder。 USE XSCJ IF EXISTS (SELECT name FROM sysobjects WHERE name = 'reminder' AND type = 'TR') DROP TRIGGER reminder GO
如何读取逻辑表？
USE skymember IF EXISTS (SELECT name FROM sysobjects WHERE name = 'adding' AND type = 'TR') DROP TRIGGER adding GO CREATE TRIGGER adding ON m_login FOR INSERT AS BEGIN insert into m_login_bak select * from inserted END GO
USE XSCJ IF EXISTS (SELECT name FROM sysobjects WHERE name = 'check_trig' AND type = 'TR') DROP TRIGGER check_trig GO CREATE TRIGGER check_trig ON XS_KC FOR INSERT AS If exists( SELECT * FROM inserted a WHERE a.学号 NOT IN (SELECT b.学号 FROM XS b) OR a.课程号 NOT IN (SELECT c.课程号 FROM KC c)) BEGIN RAISERROR ('违背数据的一致性.', 16, 1) ROLLBACK TRANSACTION --回滚操作，即回滚到insert语句执行前的状态 END
Create trigger tri_upd_产品销售 on 产品销售 for update as if update(产品编号) if (select count(*) from 产品 a inner join inserted i on a.产品编号=i.产品编号)=0 ROLLBACK TRANSACTION if update(客户编号) if (select count(*) from 销售 b inner join inserted i on b.客户编号= i.客户编号)=0 ROLLBACK TRANSACTION
2.1 创建触发器
创建触发器应该考虑以下几个问题： ①CREATE TRIGGER 语句必须是批处理中的第一个语句。 ②创建触发器的权限默认分配给表的所有者，且不能将该权限 转给其他用户。 ③触发器为数据库对象，其名称必须遵循标识符的命名规则。 ④虽然触发器可以引用当前数据库以外的对象，但只能在当前 数据库中创建触发器，且只能应用到一个表中。 ⑤不能在临时表或系统表上创建触发器。
为什么要用触发器？ • 跟踪变化 • 确保业务规则 • 自动记录更改 • 使视图可更改
触发器主要优点
触发器是自动的：当对表中的数据作了任何修改（比如 手工输入或者应用程序采取的操作）之后立即被激活。 触发器可以通过数据库中的相关表进行层叠更改。 触发器可以强制限制，这些限制比用 CHECK 约束所 定义的更复杂。
--返回用户定义的错误信息并设系统标志，记录发生错误
GO
(2) 使用企业管理器(企业分析器)创建触发器
2.2 查看、修改和删除触发器
1. 查看触发器 （１）使用企业管理器查看触发器信息 （２）使用系统存储过程查看触发器 –sp_help –sp_helptext ‘触发器名称’ –sp_depends ‘触发器名称’
(1) 使用CREATE TRIGGER命令创建触发器
其语法形式如下：
CREATE TRIGGER trigger_name --触发器的名称 ON{table|view} --创建触发器的对象名称（表或视图） [WITH ENCRYPTION] --说明是否采用加密方法 { { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } [NOT FOR REPLICATION]--说明当复制进程涉及触发器定义的表时，该触 发器不起作用 AS [if update(列名) [and | or update(列名)]] sql_statement
2.3 触发器的应用
1. 使用INSERT触发器 • INSERT触发器在对指定表进行数据插入操作时激 活。 • 激活后将插入表中的数据复制并送入一个特殊的逻辑 表（inserted表）中。 • 触发器根据inserted表的值决定如何处理。
【例16】在数据库XSCJ中创建一触发器，当向XS_KC 表插入一记录时，检查该记录的学号在XS表是否存在，检 查课程号在KC表中是否存在，若有一项为否，则不允许插 入。
[例17]：利用产品销售数据库CPXS，创建一个触发器 tri_ins_产品销售，确保插入数据时，产品编号的值必须 为产品表中已有的值。同时，该触发器还需要确保插入的 销售日期小于或等于当前日期。 注：销售数据库包含三张表，说明如下： 产品（产品编号，产品名称，价格，库存量）； 销售商（客户编号，客户名称，地区，负责人，电话）； 产品销售（产品编号，客户编号，销售日期，数量，销售 额）
语法形式
• sp_help：查看触发器的一般信息，如触发器的名称、属性、类型 和创建时间。 sp_help ‘触发器名称’ • • sp_helptext：用于查看触发器的编写文本 称’ 到的所有触发器。 sp_depends ’触发器名称’ sp_depends ‘表名’ sp_helptext ‘触发器名
[例19]：创建一个触发器tri_del_产品，当删除 产品表的数据时，若仍然存在该产品的销售信 息，则不允许删除该产品的数据。
Create trigger tri_del_产品 on 产品 for delete as if (select count(*) from 产品销售 a inner join deleted d on a.产品编号=d.产品编号)>0 ROLLBACK TRANSACTION
【例14】修改XSCJ数据库中在XS表上定义的触发器 reminder。 USE XSCJ ALTER TRIGGER reminder ON XS FOR UPDATE AS RAISERROR (“执行的操作是修改”, 16, 10) GO (注: 双引号 与 单引号)
3. 删除触发器
（１）使用系统命令DROP TRIGGER删除指定的触发器，其语 法形式如下： DROP TRIGGER { trigger } [ ,...n ] （２）删除触发器所在的表时，SQL Server将会自动删除与 该表相关的触发器。 （３）在企业管理器中，用右键单击要删除的触发器所在的 表，从弹出的快捷菜单中选择所有任务子菜单下的管理触 发器选项，则会出现触发器属性对话框。在名称选项框中 选择要删除的触发器，单击“删除”按钮，即可删除该触 发器。
数据库设计与应用
浙江工业大学计算机学院
一．存储过程 二．触发器
2
触发器
2.1 创建触发器 2.2 查看、修改和删除触发器 2.3 触发器的应用
触发器的概念
• 触发器是一种特殊类型的存储过程。
– 跟特定的操作相关联 • insert, update, delete – 跟特定的表格相关联
• 触发器通过事件触发而被执行（不能被用户调用），而 存储过程则通过存储过程名称被直接调用。 • 触发器使每个站点可以在有数据修改时自动强制执行其 业务规则。触发器可以用于 SQL Server 约束、默认值 和规则的完整性检查。
使用触发器的限制
① 在同一CREATE TRIGGER语句中，可以为多种操作（如 INSERT 和 UPDATE）定义相同的触发器操作。 ② 在触发器内可以指定任意的 SET 语句，所选择的 SET 选项在触发器 执行期间有效，并在触发器执行完后恢复到以前的设置。 ③ 触发器中不允许包含以下 T-SQL 语句： CREATE DATABASE 、ALTER DATABASE 、LOAD DATABASE 、RESTORE DATABASE 、DROP DATABASE、 LOAD LOG 、RESTORE LOG 、DISK INIT、DISK RESIZE和 RECONFIGURE ④ 触发器不能返回任何结果，为了阻止从触发器返回结果，不要在触 发器定义中包含变量赋值语句。
2. 使用UPDATE触发器•UPDATE触发器在对指定表进行数据更新操作时激 活。
•
激活后将被更新的原数据复制到deleted表，再将更新 后的新数据送入inserted表中。
•
触发器对deleted和inserted表进行检查，并决定如何 处理。
[例18]：创建一个触发器tri_upd_产品销售，当 在产品销售表中更新数据时，维护其参照完整 性，即若更新产品编号和客户编号字段时，必 须确保更新后的值包含在产品表和销售表中。