建表即插入内容如下:
mysql> CREATE TABLE T_0(id int primary key, gid int);
Query OK, 0 rows affected (5.11 sec)mysql> CREATE TABLE T_1(id int primary key, gid int);
Query OK, 0 rows affected (0.93 sec)mysql> INSERT INTO T_0(id, gid) VALUES(1, 547);
Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO T_1(id, gid) VALUES(1, 547);
Query OK, 1 row affected (0.00 sec)mysql> select * from T_0;
+----+------+
| id | gid |
+----+------+
| 1 | 547 |
+----+------+
1 row in set (0.00 sec)mysql> select * from T_1;
+----+------+
| id | gid |
+----+------+
| 1 | 547 |
+----+------+
1 row in set (0.00 sec)并建立能够在更新T_0时,同时更新T_1的触发器:mysql> DELIMITER $$
mysql> CREATE TRIGGER updt_T_1 AFTER UPDATE ON T_0
-> FOR EACH ROW BEGIN
-> UPDATE T_1 SET gid = OLD.gid WHERE id = OLD.id;
-> END;
-> $$
-> DELIMITER ;当执行 UPDATE T_0 SET gid = 777 WHERE id = 1;这样的语句时,触发器并不起作用,mysql> select * from T_0;
+----+------+
| id | gid |
+----+------+
| 1 | 777 |
+----+------+
1 row in set (0.00 sec)mysql> select * from T_1;
+----+------+
| id | gid |
+----+------+
| 1 | 547 |
+----+------+
1 row in set (0.00 sec)
但是如果再执行时:UPDATE T_0 SET gid = 666 WHERE id = 1;T_1对应的值却更新为了上次update的777:mysql> select * from T_0;
+----+------+
| id | gid |
+----+------+
| 1 | 666 |
+----+------+
1 row in set (0.00 sec)mysql> select * from T_1;
+----+------+
| id | gid |
+----+------+
| 1 | 777 |
+----+------+
1 row in set (0.00 sec)
所以,非常不理解的是,触发器中引用的到底是那一行的内容呢?
mysql> CREATE TABLE T_0(id int primary key, gid int);
Query OK, 0 rows affected (5.11 sec)mysql> CREATE TABLE T_1(id int primary key, gid int);
Query OK, 0 rows affected (0.93 sec)mysql> INSERT INTO T_0(id, gid) VALUES(1, 547);
Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO T_1(id, gid) VALUES(1, 547);
Query OK, 1 row affected (0.00 sec)mysql> select * from T_0;
+----+------+
| id | gid |
+----+------+
| 1 | 547 |
+----+------+
1 row in set (0.00 sec)mysql> select * from T_1;
+----+------+
| id | gid |
+----+------+
| 1 | 547 |
+----+------+
1 row in set (0.00 sec)并建立能够在更新T_0时,同时更新T_1的触发器:mysql> DELIMITER $$
mysql> CREATE TRIGGER updt_T_1 AFTER UPDATE ON T_0
-> FOR EACH ROW BEGIN
-> UPDATE T_1 SET gid = OLD.gid WHERE id = OLD.id;
-> END;
-> $$
-> DELIMITER ;当执行 UPDATE T_0 SET gid = 777 WHERE id = 1;这样的语句时,触发器并不起作用,mysql> select * from T_0;
+----+------+
| id | gid |
+----+------+
| 1 | 777 |
+----+------+
1 row in set (0.00 sec)mysql> select * from T_1;
+----+------+
| id | gid |
+----+------+
| 1 | 547 |
+----+------+
1 row in set (0.00 sec)
但是如果再执行时:UPDATE T_0 SET gid = 666 WHERE id = 1;T_1对应的值却更新为了上次update的777:mysql> select * from T_0;
+----+------+
| id | gid |
+----+------+
| 1 | 666 |
+----+------+
1 row in set (0.00 sec)mysql> select * from T_1;
+----+------+
| id | gid |
+----+------+
| 1 | 777 |
+----+------+
1 row in set (0.00 sec)
所以,非常不理解的是,触发器中引用的到底是那一行的内容呢?
INSERT INTO T_1(id, gid) VALUES(1, 111);
UPDATE T_0 SET gid = 777 WHERE id = 1;SELECT * FROM t_1;
结果:
id gid
1 547
CREATE TRIGGER updt_T_1 AFTER UPDATE ON T_0
FOR EACH ROW BEGIN
UPDATE T_1 SET gid = new.gid WHERE id = OLD.id;
END $$
DELIMITER ;
这样不就可以了么.
能否以此为例说明一下呢?
after update ,用OLD
+----+------+
| id | gid |
+----+------+
| 1 | 547 |
+----+------+
1 row in set (0.00 sec)mysql> select * from T_1;
+----+------+
| id | gid |
+----+------+
| 1 | 547 |
+----+------+
1 row in set (0.00 sec)mysql> CREATE TRIGGER updt_T_1 AFTER UPDATE ON T_0
-> FOR EACH ROW BEGIN
-> UPDATE T_1 SET gid = OLD.gid WHERE id = OLD.id;
-> END;
-> $$
-> DELIMITER ;以此为例。
当执行 UPDATE T_0 SET gid = 777 WHERE id = 1;这样的语句时更新完成后调用 updt_T_1 触发器。 此时 OLD.gid = 547 , NEW.gid = 777 故在触发器中
UPDATE T_1 SET gid = OLD.gid WHERE id = OLD.id;
实际是
UPDATE T_1 SET gid = 547 WHERE id = 1;
OLD指更改前的内容,NEW指更改后的内容。做了个更恰当的实验:触发表:
CREATE TABLE `T_0` (
`id` int(11) NOT NULL,
`gid` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=gb2312被触发表:
CREATE TABLE `T_1` (
`id` int(11) NOT NULL,
`gid_old` int(11) DEFAULT '0',
`gid_new` int(11) DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=gb2312insert 触发器:
DELIMITER $$
CREATE TRIGGER inst_T_1 AFTER INSERT ON T_0
FOR EACH ROW
INSERT INTO T_1(id, gid_old, gid_new) VALUES(NEW.id, NEW.gid, NEW.gid);
$$
DELIMITER ;update 触发器:
DELIMITER $$
CREATE TRIGGER updt_T_1 AFTER UPDATE ON T_0
FOR EACH ROW
UPDATE T_1 SET gid_old = OLD.gid, gid_new = NEW.gid WHERE id = OLD.id;
$$
DELIMITER ;INSERT INTO T_0(id, gid) values(1, 547);mysql> select * from T_0;
+----+------+
| id | gid |
+----+------+
| 1 | 547 |
+----+------+
1 row in set (0.00 sec)mysql> select * from T_1;
+----+---------+---------+
| id | gid_old | gid_new |
+----+---------+---------+
| 1 | 547 | 547 |
+----+---------+---------+
1 row in set (0.00 sec)
UPDATE后的结果:mysql> UPDATE T_0 SET gid = 777 WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from T_1;
+----+---------+---------+
| id | gid_old | gid_new |
+----+---------+---------+
| 1 | 547 | 777 |
+----+---------+---------+
1 row in set (0.00 sec)mysql> select * from T_0;
+----+------+
| id | gid |
+----+------+
| 1 | 777 |
+----+------+
1 row in set (0.00 sec)
希望对后来者有帮助!