mysql - mysql触发器示例-这样可以更有效地完成吗？

我目前有两张表如下：
数据表

+-----+-------------+---------------+
| uri | field1      | field2        |
+-----+-------------+---------------+
|   1 | word        | somethingelse |
|   2 | somethinge  | values        |
|   3 | change_this | test          |
+-----+-------------+---------------+

日志表

+--------+------+----------+-----------+-------------+---------------------+
| log_id | uri  | field_id | old_value | new_value   | modified_date       |
+--------+------+----------+-----------+-------------+---------------------+
|     14 | 3    | field1   | word32    | change_this | 2012-12-18 13:06:27 |
+--------+------+----------+-----------+-------------+---------------------+

当我更新field1或field2 2的值时，我会在“log”表中得到一个条目，该条目说明了哪个字段被更改了，从what到what，When，以及被更新记录的uri。
我的问题是关于触发器本身：它是正常的，还是可以更有效地完成？老实说，我以前从未写过触发器，而且是根据google找到的几个片段拼凑而成的。欢迎您的意见/反馈。

DELIMITER $$
DROP TRIGGER IF EXISTS trig_update $$
CREATE TRIGGER trig_update AFTER UPDATE on data
FOR EACH ROW
BEGIN
DECLARE data_uri VARCHAR(32);
IF (NEW.field1 != OLD.field1) THEN
SET data_uri = (SELECT uri FROM data WHERE field1 = NEW.field1);
INSERT INTO log (uri, field_id, old_value, new_value, modified_date)
VALUES (data_uri, "field1", OLD.field1, NEW.field1, NOW());
END IF;
IF (NEW.field2 != OLD.field2) THEN
SET data_uri = (SELECT uri FROM data WHERE field2 = NEW.field2);
INSERT INTO log (uri, field_id, old_value, new_value, modified_date)
VALUES (data_uri, "field2", OLD.field2, NEW.field2, NOW());
END IF;
END$$
DELIMITER ;

最佳答案

您应该消除冗余查询：

DELIMITER $$
DROP TRIGGER IF EXISTS trig_update $$
CREATE TRIGGER trig_update AFTER UPDATE on data
FOR EACH ROW
BEGIN
IF (NEW.field1 != OLD.field1) THEN
  INSERT INTO log (uri, field_id, old_value, new_value, modified_date)
  VALUES (NEW.uri, "field1", OLD.field1, NEW.field1, NOW());
END IF;
IF (NEW.field2 != OLD.field2) THEN
  INSERT INTO log (uri, field_id, old_value, new_value, modified_date)
  VALUES (NEW.uri, "field2", OLD.field2, NEW.field2, NOW());
END IF;
END$$
DELIMITER ;

关于mysql - mysql触发器示例-这样可以更有效地完成吗？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/13940033/