[数据库实验五] 审计及触发器

一、实验目的与要求：

1.了解MySQL审计功能及实现方式

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

二、实验内容：

注：

1. 在同一个触发器内编写多行代码，需要用结构begin ……end 
2. 函数current_user()获得当前登录用户名

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

①增加opertaion表

表1   opertaion表结构

字段名	字段说明	数据类型	主键	外键	非空	唯一	自增
id	ID号	int (11)	Y	N	Y	Y	Y
tablename	表名	varchar (50)	N	N	Y	N	N
opname	操作类型	varchar (50)	N	N	Y	N	N
optime	操作时间	datetime	N	N	Y	N	N
ConnUser	用户名	varchar(30)	N	N	Y	N	N

源码：

CREATE TABLE opertaion(  

    id INT(11) NOT NULL UNIQUE AUTO_INCREMENT,

    tablename VARCHAR(50) NOT NULL,

    opname VARCHAR(50) NOT NULL,

    optime DATETIME NOT NULL,

    ConnUser VARCHAR(30) NOT NULL,

    PRIMARY KEY(id)

)

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

源码：

CREATE TRIGGER insert_f AFTER INSERT ON fruits FOR EACH ROW

INSERT INTO opertaion(tablename,opname,optime,`ConnUser`)VALUES('fruits','insert',NOW(),CURRENT_USER());

CREATE TRIGGER update_f AFTER UPDATE ON fruits FOR EACH ROW

INSERT INTO opertaion(tablename,opname,optime,`ConnUser`)VALUES('fruits','update',NOW(),CURRENT_USER());

CREATE TRIGGER delete_f AFTER DELETE ON fruits FOR EACH ROW

INSERT INTO opertaion(tablename,opname,optime,`ConnUser`)VALUES('fruits','delete',NOW(),CURRENT_USER());

CREATE TRIGGER insert_oi AFTER INSERT ON orderitems FOR EACH ROW

INSERT INTO opertaion(tablename,opname,optime,`ConnUser`)VALUES('orderitems','insert',NOW(),CURRENT_USER());

CREATE TRIGGER update_oi AFTER UPDATE ON orderitems FOR EACH ROW

INSERT INTO opertaion(tablename,opname,optime,`ConnUser`)VALUES('orderitems','update',NOW(),CURRENT_USER());

CREATE TRIGGER delete_oi AFTER DELETE ON orderitems FOR EACH ROW

INSERT INTO opertaion(tablename,opname,optime,`ConnUser`)VALUES('orderitems','delete',NOW(),CURRENT_USER());

③测试：用B001帐号（即销售人员帐号）登录，在fruits表中插入一条水果信息，再查看opertaion表是否实现自动添加更新记录。

运行测试结果截图：登录B001账号，插入数据后，opertaion表格实现自动添加更新记录，结果如下图所示：

2. 实现自动更新fruits表中商品数量

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

源码：

ALTER TABLE fruits ADD quantity INT(5) DEFAULT(1000);

运行测试结果截图：

②创建触发器实现当客户下订单后，fruits表对应此商品的数量自动的发生变化（减少或增加）：

具体要求：

（1）在orderitems表中修改某个订单的商品数量后时， fruits表对应此商品的数量发生相应的改变。

（2）在orderitems表中增加一个购买详单（增加一个元组）时， fruits表对应此商品的数量发生相应的改变。

源码：

(1)修改订单数量：

CREATE TRIGGER update_q 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 =50 WHERE f_id='a1';

1. 增加一个购买详单：

CREATE TRIGGER insert_q AFTER INSERT ON orderitems FOR EACH ROW

UPDATE fruits SET quantity=quantity-new.quantity WHERE f_id=new.f_id;

INSERT INTO orderitems VALUES(50001,4,'x0',200,8.80);

运行测试结果截图：

修改前的fruits表格和orderitems表格

修改后的fruits表格和orderitems表格

三、实验小结

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

问题：在orderitems表中修改某个订单的商品数量后时， fruits表对应此商品的数量发生相应的改变，不能对新旧数据进行判断后正确执行

解决过程：触发器经过判断，新旧字段值不一样，因此调用UPDATE fruits SET quantity=quantity+old.quantity-new.quantity where f_id=new.f_id;

实现对于数据的修改。

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

错误：在通过B001账号登录时，在fruits表中插入一条水果信息，查看opertaion表是自动添加更新记录的ConnUser为root@localhost而不是B001。

原因分析：在MySQL中，CURRENT_USER()函数返回的是当前连接的用户名，而不是执行SQL语句的用户。在创建触发器时，此时的CURRENT_USER()返回的是root@localhost，因为执行这个触发器的账号是root。如果想让ConnUser字段显示插入记录的用户，需要在应用程序中获取并传递这个信息。例如，可以在插入数据时，将用户信息作为参数传递给存储过程或函数，然后在存储过程中使用这个参数来插入数据。