编程小贴士

给你的编程提供小点子


MySQL的触发器Trigger

mysql触发器是说在insert、update、delete前后你希望数据库自动帮你完成的事情。可用于数据统计,数据加工等你可以想到的事情。本文简单描述了mysql触发器的使用。

Trigger语法如下:

CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt

trigger_name:触发器名字

trigger_time:触发时机,before或after

trigger_event:触发事件,insert、update、delete

tbl_name:表名

trigger_stmt:触发器程序体,使用别名Old、New

首先创建两个库两张表

CREATE DATABASE T1;

CREATE DATABASE T2;

CREATE TABLE TB1(user1_id varchar(11),city1 varchar(30));

CREATE TABLE TB2(user2_id varchar(11),city2 varchar(30));

我们要实现跨DB同步,新数据插入到TB1同时会同步到TB2.

例子如下:

DELIMITER ||
CREATE TRIGGER T_AFTERINSERT_ON_TB1
AFTER INSERT ON T1.TB1
FOR EACH ROW
BEGIN
INSERT INTO T2.TB2(user2_id,city2) values(new.user1_id,new.city1);
END ||
DELIMITER ;

测试:
INSERT INTO TB1(user1_id,city1) values(0001,’beijing’);

查看结果:
mysql> SELECT * FROM T1.TB1;
+———-+———+
| user1_id | city1 |
+———-+———+
| 1 | beijing |
+———-+———+
1 row in set (0.00 sec)

mysql> SELECT * FROM T2.TB2;
+———-+———+
| user2_id | city2 |
+———-+———+
| 1 | beijing |
+———-+———+
1 row in set (0.00 sec)

如果需要同步删除也可以用触发器实现,我们还是使用TB1,TB2表举例建立触发器:

DELIMITER ||
CREATE TRIGGER T_AFTERDELETE_ON_TB1
AFTER DELETE ON T1.TB1
FOR EACH ROW
BEGIN
DELETE FROM T2.TB2 WHERE TB2.user2_id=old.user1_id;
END ||

测试:
DELETE FROM TB1 WHERE user1_id=’1′;

看看结果

mysql> INSERT INTO TB1(user1_id,city1) values(0002,’shanghai’);
Query OK, 1 row affected (0.08 sec)

mysql> SELECT * FROM T1.TB1;
+———-+———-+
| user1_id | city1 |
+———-+———-+
| 1 | beijing |
| 2 | shanghai |
+———-+———-+
2 rows in set (0.00 sec)

mysql> SELECT * FROM T2.TB2;
+———-+———-+
| user2_id | city2 |
+———-+———-+
| 1 | beijing |
| 2 | shanghai |
+———-+———-+
2 rows in set (0.00 sec)

mysql> DELETE FROM T1.TB1 WHERE user1_id=’1′;
Query OK, 1 row affected (0.11 sec)

mysql> SELECT * FROM T1.TB1;
+———-+———-+
| user1_id | city1 |
+———-+———-+
| 2 | shanghai |
+———-+———-+
1 row in set (0.00 sec)

mysql> SELECT * FROM T2.TB2;
+———-+———-+
| user2_id | city2 |
+———-+———-+
| 2 | shanghai |
+———-+———-+
1 row in set (0.00 sec)

那么继续看看BEFORE INSERT和BEFORE UPDATE的例子

mysql> create table T1 (id int);
Query OK, 0 rows affected (0.13 sec)

mysql> delimiter ||
mysql> CREATE TRIGGER T1_TRIGGER BEFORE INSERT ON T1 FOR EACH ROW
-> BEGIN SET @i = “China”;
-> set NEW.id = 1;
-> END;
-> ||
Query OK, 0 rows affected (0.62 sec)

mysql> select @i,T1.* from T1;
+——-+——+
| @i | id |
+——-+——+
| China | 1 |
| China | 1 |
| China | 1 |
+——-+——+
3 rows in set (0.00 sec)

mysql> create table T2 (id int,name varchar(50),rand varchar(1000));
Query OK, 0 rows affected (0.16 sec)

mysql> CREATE TRIGGER T2_TRIGGER_UP BEFORE UPDATE ON T2 FOR EACH ROW
-> BEGIN SET @a = “China”;
-> SET NEW.rand=RAND(NEW.name);
-> END;
-> ||
Query OK, 0 rows affected (0.05 sec)

这里插入之后RAND会默认显示空值.
mysql> insert into T2(id,name) values (‘1′,’beijing’);
Query OK, 1 row affected (0.02 sec)

mysql> select * from T2;
+——+———+——+
| id | name | rand |
+——+———+——+
| 1 | beijing | NULL |
+——+———+——+
1 row in set (0.00 sec)

更新之后rand列产生变化
mysql> update T2 set name=’beijing’ where id=1;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from T2;
+——+———+———————+
| id | name | rand |
+——+———+———————+
| 1 | beijing | 0.15522042769493574 |
+——+———+———————+
1 row in set (0.00 sec)

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>