关于update触发器的new和old

[netxuning]

建表即插入内容如下：

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)

所以，非常不理解的是，触发器中引用的到底是那一行的内容呢？

[WWWWA]

两表gid 字段内容一致，怎么能看出TRIGGER有问题？
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

[小小小小周]

DELIMITER $$
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 ;

这样不就可以了么.

[sql_lover]

因为你的new=old值,mysql根本没有调用更新。

[sql_lover]

错了 应该是调用了更新，楼正确。

[netxuning]

谢谢楼上几位，不过我还想知道，OLD 和 NEW到底引用的是那一行，
能否以此为例说明一下呢？

[zuoxingyu]

before update ，用NEW
after update ,用OLD

[zuoxingyu]

使用别名OLD和NEW，能够引用与触发程序相关的表中的列。OLD.col_name在更新或删除它之前，引用已有行中的1列。NEW.col_name在更新它之后引用将要插入的新行的1列或已有行的1列。

[ACMAIN_CHM]

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)

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;

[netxuning]

根据版主的讲解，无论OLD还是NEW都代指触发表的某行，跟被触发表无关。
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=gb2312

insert 触发器：
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: 0

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

希望对后来者有帮助！

[zuoxingyu]

很有帮助。接个分。

[matteliu]

good 相当好