《数据库系统原理》实验报告(9)

regionkey INTEGER,

comment VARCHAR (152));

（4）使用CHECK

使用CHECK定义订单项目表中某些属性应该满足的约束。

CREATE TABLE Lineitem(

orderkey INTEGER REFERENCES Orders(orderkey), partkey INTEGER REFERENCES Part(partkey),

suppkey INTEGER REFERENCES Supplier(suppkey), linenumber INTEGER, quantity REAL,

extendedprice REAL, discount REAL, tax REAL,

returnflag CHAR(1), linestatus CHAR(1), shipdate DATE, commitdate DATE, receiptdate DATE,

shipinstruct CHAR(25), shipmode CHAR(10),

comment VARCHAR(44)，

PRIMARY KEY (orderkey,linenumber),

FOREIGN KEY (partkey,suppkey) REFERENCES PartSupp(partkey,suppkey), CHECK (shipdate < receiptdate), /*装运日期<签收日期*/ CHECK (returnflag IN ('A','R','N'))); /*退货标记为A或R或N*/

（5）修改Lineitem的一条记录验证是否违反CHECK约束

UPDATE sales.Lineitem SET shipdate='2015-01-05',receiptdate='2015-01-01' WHERE orderkey=5005 AND linenumber=1;

四、实验心得

通过本次实验，我掌握了实体完整性的定义和维护方法，动手操作了定义实体完整性，删除实体完整性，并且可以设计SQL语句验证完整性约束是否起作用。同时，掌握了参照完整性的定义和维护方法，动手体验了定义参照完整性，并且操作了定义参照完整性的违规处理以及删除参照完整性。除此之外，还掌握了用户自定义完整性的定义和维护。此次实验使我学习了完整性相关知识，更促使我进一步学习。

实验项目名称：触发器实验 实验学时： 2 同组学生姓名： 孟陈、陈晓雪、季佰军 实验地点： 1318 实验日期： 6.9 实验成绩： 批改教师： 批改时间：

一、实验目的

掌握数据库触发器的设计和使用方法

二、实验内容和要求

定义BEFORE触发器和AFTER触发器。能够理解不同类型触发器的作用和执行原理，验证触发器的有效性。

三、实验过程

1.AFTER触发器

（1）在Lineitem表上定义一个UPDATE触发器，当修改订单明细（即修改订单明细价格extendedprice、折扣discount、税率tax）时，自动修改订单Orders的TotalPrice，以保持数据一致性。

total price=totalprice+extendedprice*（1-discount）*（1+tax）) CREATE OR REPLACE TRIGGER TRI_Price_UPDATE AFTER UPDATE OF extendedprice,discount,tax ON Lineitem FOR EACH ROW AS

DECLARE

L_valuediff REAL BEGIN

/*订单明细修改后，计算订单含税折扣价总价的修正值*/

L_valuediff=NEW.extendedprice*（1-NEW.discount）*（1+NEW.tax）- OLD.extendedprice*（1-OLD.discount）*（1+OLD.tax）； /*更新订单的含税折扣总价*/

UPDATE Orders SET totalprice=totalprice+L_valuediff WHERE orderkey=NEW.orderkey; END’

(2)在Lineitem表上定义一个INSERT触发器，当增加一项订单明细时，自动修改订单Orders的TotalPrice，以保持数据一致性。

CREATE OR REPLACE TRIGGER TRI_Lineitem_Price_INSERT AFTER INSERT ON Lineitem FOR EACH ROW AS

DECLARE

L_valuediff REAL BEGIN

L_valuediff=NEW.extendedprice*（1-NEW.discount）*（1+NEW.tax）； /*增加订单明细项后，计算订单含税折扣价总价的修正值*/ UPDATE Orders SET totalprice=totalprice+L_valuediff /*更新订单的含税折扣价总价*/ WHERE orderkey=NEW.orderkey; END;

(3)在Lineitem表上定义一个DELETE触发器，当删除一项订单明细时，自动修改订单Orders的TotalPrice，以保持数据一致性。

CREATE OR REPLACE TRIGGER TRI_Lineitem_Price_DELETE AFTER DELETE ON Lineitem FOR EACH ROW AS

DECLARE

L_valuediff REAL; BEGIN

L_valuediff=-OLD.extendedprice*（1-OLD.discount）*（1+OLD.tax）； /*删除订单明细项后，计算订单含税折扣价总家的修正值*/ UPDATE Orders SET totalprice=totalprice+L_valuediff /*更新订单的含税折扣价总价*/ WHERE orderkey=NEW.orderkey； END；

（4）验证触发器TRI_Lineitem_Price_UPDATE。 /*查看1854号订单的含税折扣价总价totalprice*/ SELECT totalprice FROM Orders

WHERE orderkey=1854；

/*激活触发器，修改1854号订单第一个明细项的税率，该税率增加0.5%*/ UPDATE Lineitem SET tax=tax+0.005

WHERE orderkey=1854 AND linenumber=1;

/*再次查看1854号订单的含税折扣总价totalprice是否有变化，如有变化，则是触发器起作用了，否则触发器没有起作用*/ SELECT totalprice FROM Orders

WHERE orderkey=1854；

2.BEFORE触发器

（1）在Lineitem表上定义一个BEFORE_UPDATE触发器，当修改订单明细中的数量(quality)时，先检查供应表PartSupp中的可用数量availqty是否足够. CREAT ORREPLACE TRIGGER TRI_Lineitem_Quality_UPDATE BEFORE UPDATE OF quality ON Lineitem FOR EACH ROW AS

DECLARE

L_valuediff INSERT； L_availqty INSERT； BEGIN

/*计算订单明细项修改时，订购数量的变化值*/ L_valuediff=NEW.quantity-OLD.quantity；

/*查询当前订单明细项对应零件供应记录中的可用数量*/ SELECT availqty INTO L_availqty FROM PartSupp

WHERE partkey=NEW.partkey AND suppkey=NEW.suppkey;

IF(L_availqty-L_valuediff >=0) THEN BEGIN

/*如果可用数量可以满足订单订购数量，则提示ENOUGH*/ RAISE NOTICE ‘Available quantity is ENOUGH‘；

/*修改当前订单明细项对应零件供应记录中的可用数量*/ UPDATE PartSupp

SET availqty=availqty-L_valuediff

WHERE partkey=NEW.partkey AND suppkey=NEW.suppkey; END; ELSE

/*如果可用数量不能满足订单订购数量，则更新过程异常中断*/ RAISE EXCEPTION ‘Available quantity is NOT ENOUGH’； END IF； END；

（2）在Lineitem表上定义一个BEFORE INSERT触发器，当插入订单明细，先检查供应表PartSupp中的可用数量availqty是否足够。

CREATE OR REPLACE TRIGGER TRI_Lineitem_Quantity_UPDATE BEFORE INSERT ON Lineitem FOR EACH ROW AS

DECLARE

L_valuediff INSERT；