mysql触发作用_MySQL触发器的作用

最新推荐文章于 2021-12-22 00:02:27 发布

嘟嘟杜杜嘟

最新推荐文章于 2021-12-22 00:02:27 发布

阅读量222

点赞数

文章标签： mysql触发作用

本文链接：https://blog.csdn.net/weixin_36269652/article/details/113214929

版权

触发器的作用

触发器是自动化的SQL程序，由插入、更新和删除事件来触发。

当面对复杂的业务关系，在程序上难以实现时。

当程序上实现较为复杂，希望在数据库内实现时。

触发器的语法

CREATE TRIGGER

{BEFORE|AFTER}

{INSERT |UPDATE |DELETE}

FOR EATH ROW

创建简单的触发器

创建数据库

mysql> use db1;

ERROR 1049 (42000): Unknown database 'db1'

mysql> create database db1;

Query OK, 1 row affected (0.07 sec)

使用数据库

mysql> use db1;

Database changed

mysql> show tables;

Empty set (0.00 sec)

创建表

mysql> create table t1 ( id int, namevarchar(10), salary int );

Query OK, 0 rows affected (0.05 sec)

查看表结构

mysql> desc t1;

+--------+-------------+------+-----+---------+-------+

+--------+-------------+------+-----+---------+-------+

| id | int(11) | YES | |NULL | |

| salary | int(11) | YES | | NULL | |

+--------+-------------+------+-----+---------+-------+

3 rows in set (0.09 sec)

创建表

mysql> create table t2 (

-> user_total int,

-> salary_total int

-> );

Query OK, 0 rows affected (0.05 sec)

查看表结构

mysql> desc t2;

+--------------+---------+------+-----+---------+-------+

+--------------+---------+------+-----+---------+-------+

| user_total | int(11) | YES | |NULL | |

| salary_total | int(11) | YES | |NULL | |

+--------------+---------+------+-----+---------+-------+

2 rows in set (0.00 sec)

插入数据

mysql> insert into t1 values (1,'jingjing',3000),

(2,'Lee',2000),

(3,'Tom',5000),

(4,'Lily',7000),

(5,'July',1000);

Query OK, 5 rows affected (0.03 sec)

Records: 5 Duplicates: 0 Warnings: 0

查看数据

mysql> select * from t1;

+------+----------+--------+

| id | name | salary |

+------+----------+--------+

| 1 | jingjing | 3000 |

| 2 | Lee | 2000 |

| 3 | Tom | 5000 |

| 4 | Lily | 7000 |

| 5 | July | 1000 |

+------+----------+--------+

5 rows in set (0.00 sec)

T2表插入数据

mysql> insert into t2 selectcount(*),sum(salary) from t1;

Query OK, 1 row affected (0.01 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql> select * from t2;

+------------+--------------+

| user_total | salary_total |

+------------+--------------+

| 5 | 18000 |

+------------+--------------+

1 row in set (0.00 sec)

创建触发器

mysql> create trigger t1_ai after inserton t1 for each row update t2 set user_total=user_total+1,salary_total=salary_total+new.salary;

Query OK, 0 rows affected (0.02 sec)

mysql> select * from t1;

+------+----------+--------+

| id | name | salary |

+------+----------+--------+

| 1 | jingjing | 3000 |

| 2 | Lee | 2000 |

| 3 | Tom | 5000 |

| 4 | Lily | 7000 |

| 5 | July | 1000 |

+------+----------+--------+

5 rows in set (0.00 sec)

mysql> select * from t2;

+------------+--------------+

| user_total | salary_total |

+------------+--------------+

| 5 | 18000 |

+------------+--------------+

1 row in set (0.00 sec)

T1表增加了一条数据

mysql> insert into t1 values(6,'jack',2000);

Query OK, 1 row affected (0.00 sec)

T2表数据发生了变化。

mysql> select * from t2;

+------------+--------------+

| user_total | salary_total |

+------------+--------------+

| 6 | 20000 |

+------------+--------------+

1 row in set (0.00 sec)

创建触发器2

mysql> create trigger t1_ad

-> after delete on t1

-> for each row

-> update t2 set

-> user_total=user_total-1,

-> salary_total=salary_total-old.salary;

Query OK, 0 rows affected (0.01 sec)

mysql> select * from t1;

+------+----------+--------+

| id | name | salary |

+------+----------+--------+

| 1 | jingjing | 3000 |

| 2 | Lee | 2000 |

| 3 | Tom | 5000 |

| 4 | Lily | 7000 |

| 5 | July | 1000 |

| 6 | jack | 2000 |

+------+----------+--------+

6 rows in set (0.00 sec)

mysql> select * from t2;

+------------+--------------+

| user_total | salary_total |

+------------+--------------+

| 6 | 20000 |

+------------+--------------+

1 row in set (0.00 sec)

删除一行数据

mysql> delete from t1 where id=6;

Query OK, 1 row affected (0.05 sec)

mysql> select * from t1;

+------+----------+--------+

| id | name | salary |

+------+----------+--------+

| 1 | jingjing | 3000 |

| 2 | Lee | 2000 |

| 3 | Tom | 5000 |

| 4 | Lily | 7000 |

| 5 | July | 1000 |

+------+----------+--------+

5 rows in set (0.00 sec)

mysql> select * from t2;

+------------+--------------+

| user_total | salary_total |

+------------+--------------+

| 5 | 18000 |

+------------+--------------+

1 row in set (0.00 sec)

创建触发器

mysql> create trigger t1_au after updateon t1 for each row update t2 set salary_total=salary_total-old.salary+new.salary;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;

+------+----------+--------+

| id | name | salary |

+------+----------+--------+

| 1 | jingjing | 3000 |

| 2 | Lee | 2000 |

| 3 | Tom | 5000 |

| 4 | Lily | 7000 |

| 5 | July | 1000 |

+------+----------+--------+

5 rows in set (0.00 sec)

mysql> select * from t2;

+------------+--------------+

| user_total | salary_total |

+------------+--------------+

| 5 | 18000 |

+------------+--------------+

1 row in set (0.00 sec)

更新数据

mysql> update t1 set salary=2000 whereid=5;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1 Changed: 1 Warnings: 0

查看

mysql> select * from t2;

+------------+--------------+

| user_total | salary_total |

+------------+--------------+

| 5 | 19000 |

+------------+--------------+

1 row in set (0.00 sec)

同一个事件无法创建两个以上的触发器。

如果想创建两个可以用after和before的方式。

After update on T1 表示 T1更新之后触发T2。

Before update on T1表示T1更新之前触发T2。

事件和触发器影响的事件在同一个事物里面。因此不会出现数据不一致的情况。

创建多执行语句触发器

Begin和end

加入了判断语句，统称为流控制语句

使用IF语句的触发器1

mysql> create table t3 (

-> id int,

-> name varchar(10),

-> gid int

-> );

Query OK, 0 rows affected (0.04 sec)

mysql>

mysql> create table t4 (

-> user_total int,

-> vip_total int

-> );

Query OK, 0 rows affected (0.05 sec)

mysql> insert into t3 values

-> (1,'joe',0),

-> (2,'Ken',1),

-> (3,'Norman',0),

-> (4,'Tobb',0),

-> (5,'July',1),

-> (6,'Zen',1),

-> (7,'Bob',0),

-> (8,'Mars',1),

-> (9,'Tom',0),

-> (10,'Lee',0);

Query OK, 10 rows affected (0.04 sec)

Records: 10 Duplicates: 0 Warnings: 0

mysql> select * from t3;

+------+--------+------+

| id | name | gid |

+------+--------+------+

| 1 | joe | 0 |

| 2 | Ken | 1 |

| 3 | Norman | 0 |

| 4 | Tobb | 0 |

| 5 | July | 1 |

| 6 | Zen | 1 |

| 7 | Bob | 0 |

| 8 | Mars | 1 |

| 9 | Tom | 0 |

| 10 | Lee | 0 |

+------+--------+------+

10 rows in set (0.00 sec)

mysql> insert into t4 values (6,4);

Query OK, 1 row affected (0.00 sec)

mysql> select * from t3;

+------+--------+------+

| id | name | gid |

+------+--------+------+

| 1 | joe | 0 |

| 2 | Ken | 1 |

| 3 | Norman | 0 |

| 4 | Tobb | 0 |

| 5 | July | 1 |

| 6 | Zen | 1 |

| 7 | Bob | 0 |

| 8 | Mars | 1 |

| 9 | Tom | 0 |

| 10 | Lee | 0 |

+------+--------+------+

10 rows in set (0.00 sec)

mysql> select * from t4;

+------------+-----------+

| user_total | vip_total |

+------------+-----------+

| 6 | 4 |

+------------+-----------+

1 row in set (0.00 sec)

创建触发器

mysql> delimiter // 把SQL语句的结束符改成//

mysql> create trigger t3_ai

-> after insert on t3

-> for each row

-> if new.gid=0 then

-> update t4 set user_total=user_total+1;

-> else update t4 set vip_total=vip_total+1;

-> end if;

-> //

Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ; 把SQL语句的结束符改为;

mysql> select * from t3;

+------+--------+------+

| id | name | gid |

+------+--------+------+

| 1 | joe | 0 |

| 2 | Ken | 1 |

| 3 | Norman | 0 |

| 4 | Tobb | 0 |

| 5 | July | 1 |

| 6 | Zen | 1 |

| 7 | Bob | 0 |

| 8 | Mars | 1 |

| 9 | Tom | 0 |

| 10 | Lee | 0 |

+------+--------+------+

10 rows in set (0.00 sec)

mysql> select * from t4;

+------------+-----------+

| user_total | vip_total |

+------------+-----------+

| 6 | 4 |

+------------+-----------+

1 row in set (0.00 sec)

更新数据

mysql> insert into t3 values(11,'jing',0);

Query OK, 1 row affected (0.01 sec)

查看

mysql> select * from t3;

+------+--------+------+

| id | name | gid |

+------+--------+------+

| 1 | joe | 0 |

| 2 | Ken | 1 |

| 3 | Norman | 0 |

| 4 | Tobb | 0 |

| 5 | July | 1 |

| 6 | Zen | 1 |

| 7 | Bob | 0 |

| 8 | Mars | 1 |

| 9 | Tom | 0 |

| 10 | Lee | 0 |

| 11 | jing | 0 |

+------+--------+------+

11 rows in set (0.00 sec)

mysql> select * from t4;

+------------+-----------+

| user_total | vip_total |

+------------+-----------+

| 7 | 4 |

+------------+-----------+

1 row in set (0.00 sec)

使用IF语句的触发器2

mysql> delimiter //

mysql>create trigger t3_ad after deleteon t3 for each row if old.gid=0 then update t4 set user_total=user_total-1; elseupdate t4 set vip_total=vip_total-1; end if;//

mysql> delimiter ;

使用IF语句的触发器3

mysql> delimiter //

mysql>create trigger t3_au after updateon t3 for each row if new.gid!=old.gid and new.gid=0 then update t4 setuser_total=user_total+1,vip_total=vip_total-1; else new.gid!=old.gid andnew.gid=1 then update t4 set user_total=user_total-1,vip_total=vip_total+1; endif;//

mysql> delimiter ;

使用CASE语句的触发器1

mysql> delimiter //

mysql>create trigger t3_au after updateon t3 for each row CASE WHEN new.gid!=old.gid and new.gid=0 then update t4 setuser_total=user_total+1,vip_total=vip_total-1; WHEN new.gid!=old.gid andnew.gid=1 then update t4 set user_total=user_total-1,vip_total=vip_total+1; endcase;//

mysql> delimiter ;

mysql> select * from t3;

+------+--------+------+

| id | name | gid |

+------+--------+------+

| 1 | joe | 0 |

| 2 | Ken | 1 |

| 3 | Norman | 0 |

| 4 | Tobb | 0 |

| 5 | July | 1 |

| 6 | Zen | 1 |

| 7 | Bob | 0 |

| 8 | Mars | 1 |

| 9 | Tom | 0 |

| 10 | Lee | 0 |

| 11 | jing | 0 |

+------+--------+------+

11 rows in set (0.01 sec)

mysql> select * from t4;

+------------+-----------+

| user_total | vip_total |

+------------+-----------+

| 7 | 4 |

+------------+-----------+

1 row in set (0.00 sec)

mysql> update t3 set gid=1 where id=11;

Query OK, 1 row affected (0.02 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from t4;

+------------+-----------+

| user_total | vip_total |

+------------+-----------+

| 6 | 5 |

+------------+-----------+

1 row in set (0.00 sec)

使用CASE语句的触发器2

mysql> delimiter //

mysql>create trigger t3_ad after deleteon t3 for each row CASE WHEN old.gid=0 then update t4 set user_total=user_total-1;WHEN old.gid=1 then update t4 set vip_total=vip_total-1; end case;//

mysql> delimiter ;

mysql> select * from t3;

+------+--------+------+

| id | name | gid |

+------+--------+------+

| 1 | joe | 0 |

| 2 | Ken | 1 |

| 3 | Norman | 0 |

| 4 | Tobb | 0 |

| 5 | July | 1 |

| 6 | Zen | 1 |

| 7 | Bob | 0 |

| 8 | Mars | 1 |

| 9 | Tom | 0 |

| 10 | Lee | 0 |

| 11 | jing | 1 |

+------+--------+------+

11 rows in set (0.00 sec)

mysql> select * from t4;

+------------+-----------+

| user_total | vip_total |

+------------+-----------+

| 6 | 5 |

+------------+-----------+

1 row in set (0.00 sec)

mysql> delete from t3 where id=11;

Query OK, 1 row affected (0.00 sec)

mysql> select * from t4;

+------------+-----------+

| user_total | vip_total |

+------------+-----------+

| 6 | 4 |

+------------+-----------+

1 row in set (0.00 sec)

使用loop语句的触发器1

每次选取一个随机值

mysql> select id,name,gid from t3 orderby rand() limit 1;

+------+------+------+

| id | name | gid |

+------+------+------+

| 6 | Zen | 1 |

+------+------+------+

1 row in set (0.00 sec)

T3表ID列加索引

mysql> alter table t3 add index (id);

Query OK, 0 rows affected (0.13 sec)

Records: 0 Duplicates: 0 Warnings: 0

Rand()依然是全表扫描

mysql> explain select id,name,gid fromt3 order by rand() limit 1\G;

*************************** 1. row***************************

id: 1

select_type: SIMPLE

table: t3

partitions: NULL

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 10

filtered: 100.00

Extra: Using temporary; Using filesort

1 row in set, 1 warning (0.00 sec)

ERROR:

No query specified

创建表

mysql> create table t5 (

-> event_date date,

-> wnnum int

-> );

Query OK, 0 rows affected (0.05 sec)

mysql> create table t6 (

-> id int,

-> name varchar(10),

-> gid int,

-> event_date date

-> );

Query OK, 0 rows affected (0.04 sec)

mysql> desc t5;

+------------+---------+------+-----+---------+-------+

+------------+---------+------+-----+---------+-------+

| wnnum | int(11) | YES | | NULL | |

+------------+---------+------+-----+---------+-------+

2 rows in set (0.00 sec)

mysql> desc t6;

+------------+-------------+------+-----+---------+-------+

+------------+-------------+------+-----+---------+-------+

| id | int(11) | YES | | NULL | |

| gid | int(11) | YES | |NULL | |

+------------+-------------+------+-----+---------+-------+

4 rows in set (0.00 sec)

触发器

mysql> create trigger t5_ai after inserton t5 for each row begin declare mnum int default 0;

-> declare wnum int;

-> declare tnum int;

-> declare rnum int;

-> declare tdate date;

-> select count(*) into tnum from t3;

-> set tdate=new.event_date;

-> set wnum=new.wnnum;

-> t5_loop:loop

-> set mnum=mnum+1;

-> set rnum=floor(1+rand()*tnum);

-> insert into t6 select id,name,gid,tdate from t3 where id=rnum;

-> if mnum>=wnum

-> then leave t5_loop;

-> end if;

-> end loop t5_loop;

-> end;

-> //

Query OK, 0 rows affected (0.01 sec)

查看

mysql> select * from t5;

Empty set (0.01 sec)

mysql> select * from t6;

Empty set (0.00 sec)

插入数据

mysql> insert into t5 values('2014-3-2',3);

Query OK, 1 row affected (0.12 sec)

查看t6表

mysql> select * from t6;

+------+--------+------+------------+

| id | name | gid | event_date |

+------+--------+------+------------+

| 2 | Ken | 1 | 2014-03-02 |

| 3 | Norman | 0 | 2014-03-02 |

| 7 | Bob | 0 | 2014-03-02 |

+------+--------+------+------------+

3 rows in set (0.00 sec)

使用Repeat语句的触发器1

mysql> create trigger t5_ai after inserton t5 for each row begin declare mnum int default 0;

-> declare wnum int;

-> declare tnum int;

-> declare rnum int;

-> declare tdate date;

-> select count(*) into tnum from t3;

-> set tdate=new.event_date;

-> set wnum=new.wnnum;

-> Repeat

-> Set mnum=mnum+1;

-> Set rnum=floor(1+rand()*tnum);

-> Insert into t6 select id,name,gid,tdate

-> From t3 where id=rnum;

-> Until mnum>=wnum

-> End repeat;

-> End;

-> //

此循环语句比LOOP简便。

使用while语句的触发器1

mysql> create trigger t5_ai after inserton t5 for each row begin declare mnum int default 0;

-> declare wnum int;

-> declare tnum int;

-> declare rnum int;

-> declare tdate date;

-> select count(*) into tnum from t3;

-> set tdate=new.event_date;

-> set wnum=new.wnnum;

-> while mnum

-> Set mnum=mnum+1;

-> Set rnum=floor(1+rand()*tnum);

-> Insert into t6 select id,name,gid,tdate

-> From t3 where id=rnum;

-> end while;

-> End;

-> //

While循环语句最简单

查看触发器

mysql> show triggers\G;

这种可以指定显示

mysql> select * frominformation_schema.triggers where TRIGGER_NAME='t1_ai'\G;

*************************** 1. row***************************

TRIGGER_CATALOG: def

TRIGGER_SCHEMA: db1

TRIGGER_NAME: t1_ai

EVENT_MANIPULATION: INSERT

EVENT_OBJECT_CATALOG: def

EVENT_OBJECT_SCHEMA: db1

EVENT_OBJECT_TABLE: t1

ACTION_ORDER: 1

ACTION_CONDITION: NULL

ACTION_STATEMENT: update t2 set user_total=user_total+1,salary_total=salary_total+new.salary

ACTION_ORIENTATION: ROW

ACTION_TIMING: AFTER

ACTION_REFERENCE_OLD_TABLE: NULL

ACTION_REFERENCE_NEW_TABLE: NULL

ACTION_REFERENCE_OLD_ROW: OLD

ACTION_REFERENCE_NEW_ROW: NEW

CREATED: 2016-01-2816:14:11.22

SQL_MODE:ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

DEFINER: root@localhost

CHARACTER_SET_CLIENT: utf8

COLLATION_CONNECTION: utf8_general_ci

DATABASE_COLLATION: latin1_swedish_ci

1 row in set (0.02 sec)

ERROR:

No query specified

删除触发器

Drop trigger trigger_name;

嘟嘟杜杜嘟

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql触发作用_MySQL触发器的作用

触发器的作用触发器是自动化的SQL程序，由插入、更新和删除事件来触发。当面对复杂的业务关系，在程序上难以实现时。当程序上实现较为复杂，希望在数据库内实现时。触发器的语法CREATE TRIGGER {BEFORE|AFTER}{INSERT |UPDATE |DELETE}ON FOR EATH ROW创建简单的触发器创建数据库mysql> use db1;ERROR 1049 (4200...
复制链接

扫一扫