Innodb 存储引擎 学习笔记 -触发器

触发器作用：在执行INSERT、DELETE、UPDATE命令转换 自动调用SQL命令或存储过程。

触发器也是实现约束的一种方法！

创建一张用户消费表，每次用户购买一样物品后其金额都是递减的，若此时有“不怀好意”的用户做了一个减去负数的操作，这样金额就会增加，从逻辑业务来说，这一定是错的所以我们在这里设置一个触发器，当用户执行了减去负数的操作时记录下来，在表usercash_err_log中。

mysql> create table uesrcash(
    -> userid int not null,
    -> cash int unsigned not null);
Query OK, 0 rows affected (0.10 sec)

mysql> insert into usercash select 1,1000;
Query OK, 1 row affected (0.01 sec)

 

mysql> update usercash
    -> set cash =cash -(-20)
    -> where userid =1;
Query OK, 1 row affected (0.09 sec)

创建错误操作表

mysql> create table usercash_error_log(
    -> userid int not null,
    -> old_cash int unsigned not null,
    -> new_cash int unsigned not null,
    -> user varchar(30),
    -> time DATETIME);
Query OK, 0 rows affected (0.13 sec)

 创建触发器：

mysql> delimiter $$
mysql>  create trigger tgr_usercash_update before update on usercash
    ->     for each row
    ->     begin
    ->     if new.cash - old.cash >0 then
    ->     insert into usercash_error_log
    ->     select old.userid,old.cash,new.cash,USER(),NOW();
    ->     set new.cash = old.cash;
    ->     end if;
    ->     end;
    ->    $$

现在，我们再次试着让cash = cash - (-20)

mysql> UPDATE usercash
    -> set cash = cash-(-20);
Query OK, 0 rows affected (0.11 sec)
Rows matched: 2  Changed: 0  Warnings: 0


mysql> UPDATE usercash
    -> set cash = cash-(-20)
    -> where userid =2;

mysql> select * from usercash;
+--------+------+
| userid | cash |
+--------+------+
|      1 | 1020 |
|      2 | 1000 |
+--------+------+

mysql> select * from usercash_error_log;
+--------+----------+----------+----------------+---------------------+
| userid | old_cash | new_cash | user           | time                |
+--------+----------+----------+----------------+---------------------+
|      1 |     1020 |     1040 | root@localhost | 2019-03-21 13:54:42 |
|      2 |     1000 |     1020 | root@localhost | 2019-03-21 13:54:42 |
|      2 |     1000 |     1020 | root@localhost | 2019-03-21 13:55:39 |
+--------+----------+----------+----------------+---------------------+
3 rows in set (0.00 sec)

可以看到，cash值并没有改变

错误操作都被记录到 usercash_error_log 里了。