07-综合案例-触发程序的使用

10.4 综合案例——触发程序的使用

本节视频教学录像：12分钟

本章介绍了触发程序的创建语法及删除语法，要想熟练掌握触发程序，就需要在使用过程中弄清楚触发程序的结构，清楚触发程序的触发时间（BEFORE或AFTER）及触发事件（INSET、DELETE或UPDATE）。使用触发程序可以实现数据库的审计操作，记载数据的变化、操作数据库的用户、数据库的操作、操作时间等。

【范例10-6】

使用触发程序审计雇员表的工资变化。

mysql>create table empsa ->(empno int, -> empname varchar(20), -> empsal float); Query OK,0 rows affected(0.66 sec) mysql>create table ad ->(empno int, -> oempsal float, ->nempsal float, ->user varchar(20), ->time timestamp); Query OK, 0 rows affected (0.50 sec) mysql> create trigger t1 -> after update -> on empsa -> for each row -> begin -> insert into ad -> values(old.empno,old.empsal,new.empsal,current_user(),now()); -> end// Query OK, 0 rows affected (0.46 sec) mysql> insert into empsa values(1,'mike',1000)// Query OK, 1 row affected (0.10 sec) mysql> select from empsa// +-------+---------+--------+ | empno | empname | empsal | +-------+---------+--------+ | 1|mike | 1000| +-------+---------+--------+ 1 row in set (0.03 sec) mysql> update empsa -> set empsal=1050 -> where empno=1// Query OK, 1 row affected (0.23 sec) Rows matched:1 Changed:1 Warnings:0 mysql> select from ad// +-------+---------+---------+----------------+---------------------+ |empno|oempsal|nempsal|user |time | +-------+---------+---------+----------------+---------------------+ | 1| 1000| 1050|root@localhost|2015-04-28 09:28:44| +-------+---------+---------+----------------+---------------------+ 1 row in set (0.00 sec)

实现过程如下。

(1)创建雇员表empsa。其中，empno为雇员编号empsal；empname为雇员姓名empsal；empsal雇员的工资字段。(2) 创建审计表ad。

create table empsa (empno int, empname varchar(20), empsal float);

create table ad (empno int, oempsal float, nempsal float, user varchar(20), time timestamp);

其中，oempsal字段记录更新前的工资旧值；nempsal更改后的工资新值；user为操作的用户；time字段保存更改的时间。

⑶创建触发程序。

create trigger t1 after update on empsa for each row begin insert into ad values(old.empno,old.empsal,new.empsal,current_user(),now());

其中，current_user()函数获取当前的用户名称；now() 函数获取当前的时间。

⑷验证触发程序。

insert into empsa values(1,'mike',1000); update empsa set empsal=1050 where empno=1

通过INSERT语句向表中插入了数据，empno为1，empsa为1000，然后执行UPDATE语句，将empno等于1的员工资empsa修改为1050，然后查看审计表ad,ad表中存储了empsa修改前的旧值1000，同时存储了empsa修改后的旧值1050,并记录了修改的用户为root及修改的时间，实现了查询的要求。

通过触发程序可以实现删除主表信息时，级联删除子表中引用主表的相关记录。

【范例10-7】

创建一个部门表dept和雇员表emp，当删除dept中的一个部门信息后，级联删除emp表中属于该部门的雇员信息。

mysql>create table dept ->(dno int, -> dname varchar(20)); Query OK,0 rows affected(0.42 sec) mysql>insert into dept values(1,'工程部'),(2,'财务部'),(3,'后勤部'); mysql>select ->from dept// +------+--------+ |dno |dname | +------+--------+ | 1|工程部| | 2|财务部| | 3|后勤部| +------+--------+ rows in set (0.00 sec) mysql> create table emp -> (eno int, -> ename varchar(20), -> dno int); Query OK, 0 rows affected (0.64 sec) insert into emp values(1,'王明'，1),(2,'李小程'，1),(3,'赵坤',2); mysql> select -> from emp// +------+--------+------+ |eno |ename |dno | +------+--------+------+ | 1|王明 | 1| | 2|李小程| 1| | 3|赵坤 | 2| +------+--------+------+ 3 rows in set (0.02 sec) mysql> delimiter // mysql> create trigger tri_dept -> after delete -> on dept -> for each row -> begin -> delete from emp where dno=old.dno; -> end // Query OK, 0 rows affected (0.14 sec) mysql> delete from dept where dno=1// Query OK, 1 row affected (0.06 sec) mysql> select * from emp// +------+-------+------+ |eno |ename|dno | +------+-------+------+ | 3|赵坤 | 2| +------+-------+------+ 1 row in set (0.00 sec)

实现过程如下。

⑴创建部门表dept(dno,dname), 字段分别为部门编号和部门名称，并插入了三行数据。

mysql> create table dept (dno int, dname varchar(20)) mysql> insert into dept values(1,'工程部'),(2,'财务部'),(3,'后勤部')

⑵创建雇员表emp(eno,ename,dno)，字段分别为雇员编号、雇员姓名、部门编号，并插入三行数据。

mysql> create table emp (eno int, ename varchar(20), dno int) insert into emp values(1,'王明'，1),(2,'李小程'，1),(3,'赵坤',2)

⑶创建触发器。 create trigger tri_dept after delete on dept for each row begin delete from emp where dno=old.dno; end

触发器使用delete from emp where dno=old.dno实现删除部门中的雇员信息。

⑷验证触发器。

mysql> delete from dept where dno=1 mysql> select * from emp |eno |ename|dno | +------+-------+------+ | 3|赵坤 | 2| +------+-------+------+ 1 row in set (0.00 sec)

删除了部门1的信息，部门1的两个雇员也被级联删除，实现了查询要求。

技巧 在创建表时可以使用外键约束加on delete cascade和on delete cascade实现主表和子表的级联删除和级联更新。