MySQL 触发器与 if 语句尝试查询不同的表以插入值

Question

我正在设置一些触发器，以便如果表上的值有更新，它将触发将一些字段插入到更改日志表中。在原始更新中，我设置了外键，以便更改键，从而根本不使用该值。

以下是我的 SQL，但我不断收到语法错误:

DROP TRIGGER IF EXISTS `history_hosts_modify`;
DELIMITER //
CREATE TRIGGER `history_hosts_modify` BEFORE UPDATE ON `hosts`
  FOR EACH ROW BEGIN
    IF OLD.hostname != NEW.hostname THEN
      INSERT INTO changelogs (cid, remoteid, cat, action, oldval, newval, modified) VALUES(NULL, OLD.id, 'HOST', 'MOD', OLD.hostname, NEW.hostname, NOW());
    END IF;
    IF OLD.clients_id != NEW.clients_id THEN
      DECLARE TEMP1, TEMP2 INT;
      SELECT client FROM clients WHERE id=OLD.clients_id INTO TEMP1;
      SELECT client FROM clients WHERE id=NEW.clients_id INTO TEMP2;
      INSERT INTO changelogs (cid, remoteid, cat, action, oldval, newval, modified) VALUES(NULL, OLD.id, 'HOST', 'MOD', TEMP1, TEMP2, NOW());
    END IF;
  END
//
DELIMITER ;

你能帮我吗？

Answer 1

13.6.3 DECLARE Syntax

...

DECLARE is permitted only inside a BEGIN ... END compound statement and must be at its start, before any other statements.

...

尝试:

DROP TRIGGER IF EXISTS `history_hosts_modify`;

DELIMITER //

CREATE TRIGGER `history_hosts_modify` BEFORE UPDATE ON `hosts`
FOR EACH ROW
BEGIN
    DECLARE TEMP1, TEMP2 INT;
    IF OLD.hostname != NEW.hostname THEN
        INSERT INTO changelogs (cid, remoteid, cat, action, oldval, newval, modified)
        VALUES
        (NULL, OLD.id, 'HOST', 'MOD', OLD.hostname, NEW.hostname, NOW());
    END IF;
    IF OLD.clients_id != NEW.clients_id THEN
        -- DECLARE TEMP1, TEMP2 INT;
        SELECT client FROM clients WHERE id=OLD.clients_id INTO TEMP1;
        SELECT client FROM clients WHERE id=NEW.clients_id INTO TEMP2;
        INSERT INTO changelogs (cid, remoteid, cat, action, oldval, newval, modified)
        VALUES
        (NULL, OLD.id, 'HOST', 'MOD', TEMP1, TEMP2, NOW());
    END IF;
END//

DELIMITER ;