mysql触发器

1,触发器(触发函数的定义)

 触发器是保存在数据库中的程序，用于监听某个表的行为,一旦这个行为发生,就去执行相应的sql语句

分类：

前触发器：在数据更新前触发，限制性触发器的内容，在执行数据操作的内容（一个表中只能有一个前触发器）

后触发器：在数据更新后自动执行触发器的内容（可以有多个）

这些行为包括：增加,删除,修改

2,定义触发器的语法：

create trigger 触发器名 触发器事件 ON 监听的表名 for each row 要执行的操作（sql语句）

触发器事件组成：

触发器发生的时间:before  after某个行为发生之前还是之后

事件的内容:增加,删除,修改

对于insert语句只有new是合法的

对于delete语句只有old是合法的

update可以与new或old同时使用

new关键字----代表新的记录(新增加的记录，或者更新后的记录)

old关键字----代表的是过去的记录(被删除掉的记录,更新前的记录)

不管是new还是old都是代表记录的，触发程序所监听表的某个记录

 

mysql> select * from goods;
+----------+------------+------------+--------------+
| goods_id | goods_name | shop_price | goods_number |
+----------+------------+------------+--------------+
|        1 | 苹果          |       2.00 |           20 |
|        2 | 梨           |       2.00 |           30 |
|        3 | 香蕉         |       2.00 |           40 |
+----------+------------+------------+--------------+
3 rows in set (0.02 sec)

mysql> create trigger n after insert on orderr for each row update goods set goo
ds_number=goods_number-3 where goods_id=1;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into orderr values('1','苹果','2');
Query OK, 1 row affected (0.03 sec)

mysql> select * from goods;
+----------+------------+------------+--------------+
| goods_id | goods_name | shop_price | goods_number |
+----------+------------+------------+--------------+
|        1 | 苹果          |       2.00 |           17 |
|        2 | 梨           |       2.00 |           30 |
|        3 | 香蕉         |       2.00 |           40 |
+----------+------------+------------+--------------+
3 rows in set (0.00 sec)

mysql> drop trigger n;
Query OK, 0 rows affected (0.01 sec)

mysql> create trigger n after insert on orderr for each row update goods set goo
ds_number=goods_number-new.buy_number where goods_id = new.goods_id;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into orderr values('2','梨','3');
Query OK, 1 row affected (0.00 sec)

mysql> select * from goods;
+----------+------------+------------+--------------+
| goods_id | goods_name | shop_price | goods_number |
+----------+------------+------------+--------------+
|        1 | 苹果          |       2.00 |           17 |
|        2 | 梨           |       2.00 |           27 |
|        3 | 香蕉         |       2.00 |           40 |
+----------+------------+------------+--------------+
3 rows in set (0.00 sec)

mysql> drop trigger back_unmber;
Query OK, 0 rows affected (0.01 sec)

mysql> create trigger back_number after delete on orderr for each row update goo
ds set goods_number = goods_number+ old.buy_number where goods_id=old.goods_id;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into orderr values('1','苹果','2');
Query OK, 1 row affected (0.00 sec)

mysql> insert into orderr values('2','梨','4');
Query OK, 1 row affected (0.00 sec)

mysql> select * from orderr;
+----------+------------+------------+
| goods_id | goods_name | buy_number |
+----------+------------+------------+
|        2 | 梨           |          4 |
|        1 | 苹果          |          2 |
+----------+------------+------------+
2 rows in set (0.00 sec)

mysql> select * from goods;
+----------+------------+------------+--------------+
| goods_id | goods_name | shop_price | goods_number |
+----------+------------+------------+--------------+
|        1 | 苹果          |       2.00 |           15 |
|        2 | 梨           |       2.00 |           23 |
|        3 | 香蕉         |       2.00 |           40 |
+----------+------------+------------+--------------+
3 rows in set (0.00 sec)

mysql> delete from orderr where goods_id=2;
Query OK, 1 row affected (0.00 sec)

mysql> select * from goods;
+----------+------------+------------+--------------+
| goods_id | goods_name | shop_price | goods_number |
+----------+------------+------------+--------------+
|        1 | 苹果          |       2.00 |           15 |
|        2 | 梨           |       2.00 |           27 |
|        3 | 香蕉         |       2.00 |           40 |
+----------+------------+------------+--------------+
3 rows in set (0.00 sec)

mysql>