回顾MySQL Trigger

最近使用到Tigger,所以写了几个例子回顾一下.

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)

这篇只简单的举例,如有错误请指正谢谢..今天先到此了..^_^