MySQL常用操作__触发器

触发器是一种特殊的存储过程，再插入，删除或修改表格时触发
他比数据库本身的标准功能有更精细和更复杂的数据控制能力


监视事件update,delete,insert
出发时间before,after
触发事件update,delete,insert

创建
在对sh表插入后对slh表进行插入
CREATE TRIGGER tr_1 AFTER INSERT
ON sh FOR EACH ROW
BEGIN
		INSERT slh(LEVEL) VALUES(123);
END;
$;
mysql> select * from sh;
+----+----------+-------+
| id | username | score |
+----+----------+-------+
|  1 | 1        |    11 |
|  2 | 2        |    13 |
|  3 | 3        |    14 |
|  4 | 4        |    15 |
|  5 | 5        |    16 |
|  6 | 6        |    15 |
|  7 | 7        |    18 |
|  8 | 12s      |    91 |
|  9 | 2s       |    93 |
| 10 | 3s       |    84 |
| 11 | 4s       |    75 |
| 12 | 5s       |    96 |
| 13 | 6s       |    75 |
| 14 | 7s       |    88 |
| 15 | wxjj     |    78 |
+----+----------+-------+
15 rows in set (0.00 sec)
mysql> select * from slh;
+----+-------+
| id | level |
+----+-------+
|  1 |   122 |
|  2 |    80 |
|  3 |    70 |
|  4 |   123 |
+----+-------+
4 rows in set (0.00 sec)


在满足对sh删除之前，先删除slh的数据
CREATE TRIGGER tr_2 BEFORE DELETE
ON sh FOR EACH ROW
BEGIN
		DELETE FROM slh WHERE LEVEL=123;
END;
$;
delete from sh where id=1;
mysql> select * from slh;
+----+-------+
| id | level |
+----+-------+
|  1 |   122 |
|  2 |    80 |
|  3 |    70 |
+----+-------+
3 rows in set (0.00 sec)


update-old-new
CREATE TRIGGER tr_2 AFTER UPDATE  ON slh FOR EACH ROW
BEGIN
	UPDATE sh SET score=new.level WHERE id=old.id;
END;
$;
new.level来自slh的更新level
id是sh本身的id
因为并没有对slh的id字段进行更新操作
所以old.id 就是slh本身的id，当作where 的条件判断来使用
UPDATE slh SET level=99 WHERE id =3;
mysql> UPDATE slh SET level=99 WHERE id =3;
Query OK, 1 row affected (0.07 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from slh;
+----+-------+
| id | level |
+----+-------+
|  1 |   122 |
|  2 |    80 |
|  3 |    99 |
+----+-------+
3 rows in set (0.00 sec)
id=3 的score变成99了
mysql> select * from sh;
+----+----------+-------+
| id | username | score |
+----+----------+-------+
|  2 | 2        |    13 |
|  3 | 3        |    99 |
|  4 | 4        |    15 |
|  5 | 5        |    16 |
|  6 | 6        |    15 |
|  7 | 7        |    18 |
|  8 | 12s      |    91 |
|  9 | 2s       |    93 |
| 10 | 3s       |    84 |
| 11 | 4s       |    75 |
| 12 | 5s       |    96 |
| 13 | 6s       |    75 |
| 14 | 7s       |    88 |
| 15 | wxjj     |    78 |
+----+----------+-------+
14 rows in set (0.00 sec)





SHOW TRIGGERS;
SELECT * from information_schema.TRIGGERS WHERE TRIGGER_NAME='name'\G;

DROP TRIGGER 定义的触发器名字;