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

触发器的作用

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

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

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

触发器的语法

CREATE TRIGGER 

{BEFORE|AFTER}

{INSERT |UPDATE |DELETE}

ON

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;

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

| Field | Type        | Null | Key |Default | Extra |

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

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

| name  | varchar(10) | 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;

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

| Field        | Type    | Null | Key | Default | Extra |

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

| 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>

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;

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

| Field     | Type    | Null | Key | Default |Extra |

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

| event_date | date    | YES |     | NULL    |      |

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

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

2 rows in set (0.00 sec)

mysql> desc t6;

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

| Field     | Type        | Null | Key |Default | Extra |

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

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

| name      | varchar(10) | YES  |     | NULL   |       |

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

| event_date | date        | 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
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值