第06章_存储过程与触发器.ppt

第6章 存储过程、触发器和数据完整性 使用存储过程的动机 基本概念 创建存储过程 例：创建一个最简单的存储过程： 例：带参数的存储过程： 执行存储过程 例：执行带参数的sp_getemp;2存储过程 存储过程的返回值和状态信息 存储过程的修改和删除 修改存储过程的语句是（一般格式）： ALTER PROC[edure] procedure_name [ ; number ] [ @parameter data_type [ = default ], …?] AS sql_statement 删除存储过程的语句是： DROP PROC[edure] procedure_name 注意：删除存储过程的语句中不能指定序号。也就是说，该语句将同时删除同名的所有存储过程。 要用好存储过程 6.2 触发器 触发器的用途 触发器是依附于表的数据库对象 一个触发器和三部分内容有关： 定义触发器的表 激活触发器的数据操作语句 触发器要采取的动作 建立触发器的语句 例：建立一个简单的触发器。 插入视图和删除视图 删除类触发器 例：定义一个触发器，使得当删除仓库记录时，同时将所属所有职工记录的仓库号字段值置为空值NULL： 插入类触发器 例：对职工表的插入操作定义一个触发器，使得当插入职工记录时，检查相应的仓库元组是否存在，如果不存在则撤消所做的插入操作。 更新类触发器 例：对职工表的更新操作定义一个触发器，使得当职工变换所属仓库时，检查相应的仓库元组是否存在，如果不存在则撤消所做的更新操作，如果新的仓库号是WH2则将工资提高10%。 对Products表更新单价字段值时，如果超过10%则拒绝更新。 对Products表更新单价字段值时，如果超过10%则拒绝更新。 CREATE TRIGGER up_price ON Products FOR UPDATE AS IF UPDATE(UnitPrice) DECLARE @new real,@old real SELECT @new=UnitPrice FROM inserted SELECT @old=UnitPrice FROM deleted IF ABS(@new-@old)/@old0.1 BEGIN RAISERROR (‘单价更新超过10%！, 16, 1) ROLLBACK TRANSACTION END 对Order Details表更新单价字段值时，如果超过10%则创建例外记录。 假设用OrderDetails表存储例外记录 SELECT * INTO OrderDetails FROM Order Details WHERE 1=0 对Order Details表更新单价字段值时，如果超过10%则创建例外记录。 CREATE TRIGGER upd_price ON Order Details FOR UPDATE AS IF UPDATE(UnitPrice) DECLARE @new real,@old real SELECT @new=UnitPrice FROM inserted SELECT @old=UnitPrice FROM deleted IF ABS(@new-@old)/@old0.1 BEGIN INSERT INTO OrderDetails SELECT * FROM deleted INSERT INTO OrderDetails SELECT * FROM inserted END 复杂完整性约束举例 假设有如下关系模式： 管理(仓库号，设备号，职工号) 它所包含的语义是： 一个仓库可以有多个职工； 一名职工仅在一个仓库工作； 在每个仓库一种设备仅由一名职工保管（但每名职工可以保管多种设备）。 显然该关系模式的关键字是(仓库号,设备号)。进一步假设，该关系中已经有元组： (WH1,P1,E1) (WH1,P2,E1) 现在接着要插入元组(WH2,P2,E1)，注意：这种操作显然违反了语义“一名职工仅在一个仓库工作”，但它不违反实体完整性约束，一般的域完整性约束也无济于事。这时则只能通过触发器来实现数据完整性约束。 建立触发器 CREATE TRIGGER wh_emp ON 管理 FOR INSERT,UPDATE AS DECLARE @emp CHAR(6),@wh CHAR(6) SELECT @wh=仓库号,@emp=职工号 FROM inserted IF (SELECT COUNT(*) FROM 管理 WHERE 职工号=@emp AND 仓库号@wh)