mysql触发器

触发器功能:当操作了某张表时，可以同时触发一些动作/行为。

6.0 mysql触发器

需求： 当向员工表插入一条记录时，希望mysql自动同时往日志表插入数据
mysql> create table test_log(
id int primary key auto_increment,
content varchar(100));
Query OK, 0 rows affected (0.53 sec)

mysql> desc test_log;
+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| content | varchar(100) | YES | | NULL | |
+---------+--------------+------+-----+---------+----------------+
2 rows in set (0.06 sec)
mysql> select * from test_log;
Empty set (0.00 sec)

-- 创建触发器(添加)
-- 向员工表插入一条记录
CREATE TRIGGER tri_empAdd AFTER INSERT ON employee FOR EACH ROW
INSERT INTO test_log(content) VALUES('员工表插入了一条记录');

-- 插入数据
mysql> INSERT INTO employee(id,empName,deptId) VALUES(7,'孙悟空',1);

mysql> select * from employee;
+----+-----------+--------+--------+
| id | empName | deptId | bossId |
+----+-----------+--------+--------+
| 1 | 张三 | 1 | NULL |
| 2 | 李四 | 1 | 1 |
| 3 | 王五 | 2 | 2 |
| 4 | 陈六 | 3 | 3 |
| 7 | 孙悟空 | 1 | NULL |
+----+-----------+--------+--------+
5 rows in set (0.00 sec)

mysql> select * from test_log;
+----+--------------------------------+
| id | content |
+----+--------------------------------+
| 1 | 员工表插入了一条记录 |
+----+--------------------------------+
1 row in set (0.00 sec)

-- 创建触发器(修改)
-- 当往员工表修改一条记录时
CREATE TRIGGER tri_empUpd AFTER UPDATE ON employee FOR EACH ROW
INSERT INTO test_log(content) VALUES('员工表修改了一条记录');

-- 修改
UPDATE employee SET empName='eric' WHERE id=7;

-- 创建触发器(删除)
-- 当往员工表删除一条记录时
CREATE TRIGGER tri_empDel AFTER DELETE ON employee FOR EACH ROW
INSERT INTO test_log(content) VALUES('员工表删除了一条记录');

-- 删除
DELETE FROM employee WHERE id=7;