数据库实验五：触发器

一、实验目的与要求

掌握触发器的工作原理、定义及操作方法

二、实验内容

1.自动保存对所有表的插入、更新、删除操作

自动保存对所有表（至少fruits表和customers表）的插入、更新、删除操作到operation表中。

①增加operation表

源码：

CREATE TABLE operation(
       id  INT(11) NOT NULL UNIQUE AUTO_INCREMENT, 
       tablename VARCHAR(50) NOT NULL,  
       opname VARCHAR(50) NOT NULL,
       optime DATETIME NOT NULL,
       PRIMARY KEY (id) 
);

②创建基于fruits表和customers表的插入、更新、删除触发器,该类触发器的功能是在执行相关操作时把操作相关信息写入opertaion表中。例如在fruits表中成功插入一项新的元组后，在opertaion表中自动增加一个元组，该元组描述了是对fruits表进行了插入操作。

源码：

#fruits表增加事件触发器insert_f
CREATE TRIGGER insert_f AFTER INSERT ON fruits FOR EACH ROW
	INSERT INTO operation(tablename,opname,optime)
  VALUES('fruits','增加一项',NOW());
#fruits表更新事件触发器update_f
CREATE TRIGGER update_f AFTER UPDATE ON fruits FOR EACH ROW
  INSERT INTO operation(tablename,opname,optime)
  VALUES('fruits','更新一项',NOW());
#fruits表删除事件触发器delete_f
CREATE TRIGGER delete_f AFTER DELETE ON fruits FOR EACH ROW
	INSERT INTO operation(tablename,opname,optime)
	VALUES('fruits','删除一项',NOW());

#customer表增加事件触发器insert_c
CREATE TRIGGER insert_c AFTER INSERT ON customers FOR EACH ROW
	INSERT INTO operation(tablename,opname,optime)
	VALUES('customers','增加一项',NOW());
#customers表更新事件触发器update_c
CREATE TRIGGER update_c AFTER UPDATE ON customers FOR EACH ROW
  INSERT INTO operation(tablename,opname,optime)
  VALUES('customers','更新一项',NOW()); 
#customers表删除事件触发器delete_c
CREATE TRIGGER delete_c AFTER DELETE ON customers FOR EACH ROW
	INSERT INTO operation(tablename,opname,optime)
	VALUES('customers','删除一项',NOW());

fruits表测试：

#fruits表测试
INSERT INTO  fruits (f_id,s_id,f_name,f_price) 
	VALUES (77,107,'Sariel',1.2);

UPDATE fruits SET f_name='fox',f_price=2.10 WHERE f_id='77';

DELETE FROM fruits WHERE f_id='77';

customers表测试：

INSERT INTO  customers (c_id,c_name,c_address,c_city,c_zip,c_contact,c_email) 
	VALUES (10006,'Evan','Warson Inc','GQ City',666,'me','666@123');

UPDATE customers SET c_name='rabbit',c_email='123@666' WHERE c_id='10006';

DELETE FROM customers WHERE c_id='10006';

运行测试结果截图：

fruits表测试：


customers表测试：

2.在表中增加一个属性

在fruits表中增加一个属性quantity(库存商品数量) ，数据类型int（5），默认值为1000。

源码：

#给fruits表增加属性quantity
ALTER TABLE fruits ADD quantity 
	INT(5) DEFAULT(1000);

运行测试结果截图：

3.创建触发器

创建触发器实现当客户下订单后，fruits表对应此商品的数量自动的发生变化（减少或增加）：
具体要求：
①在orderitems表中修改某个订单的商品数量后时， fruits表对应此商品的数量发生相应的改变。
②在orderitems表中增加一个购买详单（增加一个元组）时， fruits表对应此商品的数量发生相应的改变。

源码①：

#orderitems表更新数量触发器
CREATE TRIGGER update_num AFTER UPDATE ON orderitems FOR EACH ROW
	UPDATE fruits 
	SET quantity=quantity+old.quantity-new.quantity 
	WHERE f_id=new.f_id;
#更新数量测试
UPDATE orderitems SET quantity=20 WHERE f_id='a1';

源码②：

#orderitems表增加详单触发器
CREATE TRIGGER in_order AFTER INSERT ON orderitems FOR EACH ROW
  UPDATE fruits 
  SET quantity=quantity-new.quantity 
WHERE f_id=new.f_id;
#增加详单测试
INSERT INTO orderitems VALUES (30011,2,'b2',50,7.60);

运行测试结果截图：

测试①：

测试②：

三、实验小结

1.实验中遇到的问题及解决过程

遇到的问题：一开始没理解最后一题的题意，增加一个购买详单时没有选择插入一整个元组，而是单纯改变了一个数值，导致结果跟上一小题的做法没有区别。
解决过程：重新理解了题意，改为在表中添加元组。

2.实验中产生的错误及原因分析

错误：增加数据的时候失败

原因分析：忘记了o_num为主键，数值和之前的重复导致主键冲突，故插入失败。