MySQL 触发器 - 获取对象的 DDL 时出错

Question

我创建了两个触发器，但每当我尝试更改“计算机”表架构时都会收到错误消息:

触发 trgMarkUpd:

DELIMITER $$

CREATE TRIGGER `trgMarkUpd`
BEFORE UPDATE on Computers
FOR EACH ROW
BEGIN
    If old.OutofSync = 3 THEN
     SET new.OutofSync = 2;
     END IF;
END;
DELIMITER ;

触发 trgMarkNew:

 DELIMITER $$

    CREATE TRIGGER `trgMarkNew`
    BEFORE INSERT on Computers
    FOR EACH ROW
   BEGIN
    SET new.OutofSync = 1 ;   
    END;
    DELIMITER ;

TRIGGER trgSoftwareLicenseDetails:

    DELIMITER $$

    CREATE TRIGGER `trgSoftwareLicenseDetails`
    BEFORE UPDATE on SoftwareLicenseDetails
    FOR EACH ROW
    BEGIN

    IF new.Flag = 0 THEN
        INSERT INTO audithistory (audit_date, audit_field, audit_oldvalue, audit_changelog_fk, audit_newvalue, audit_assetid_fk) VALUES (Now(),''Software License Details'', (SELECT Title FROM SoftwareTypes WHERE ID = 
        (SELECT SoftwareNameFK FROM SoftwareLicenseDetails
        WHERE  ComputerFK=new.ComputerFK
        ORDER BY ID Desc
        LIMIT 1)), (SELECT MAX(ID) FROM Changelog as ChangelogID ), ''License Added'',new.ComputerFK);

    ELSEIF new.Flag = 1 THEN
        INSERT INTO audithistory (audit_date, audit_field, audit_oldvalue, audit_changelog_fk, audit_newvalue, audit_assetid_fk) VALUES (Now(),''Software License Details'', ''N/A'', (SELECT MAX(ID) FROM Changelog as ChangelogID ), ''License Deleted'',old.ComputerFK);
    END IF;

END;
    DELIMITER ;

有人能告诉我 SQL 有什么问题吗？？

请注意，我在创建触发器时没有收到任何错误消息。

Answer 1

语法的几个问题:

• 以 $$(DELIMITER)结束所有触发器:

DELIMITER $$

CREATE TRIGGER `trgMarkUpd` BEFORE UPDATE on Computers
FOR EACH ROW
BEGIN
   If old.OutofSync = 3 THEN
      SET new.OutofSync = 2;
   END IF;
-- END;
END$$

DELIMITER ;

• 在字符串中使用单引号:

...
-- LIMIT 1)), (SELECT MAX(ID) FROM Changelog as ChangelogID ), ''License Added'',new.ComputerFK);
LIMIT 1)), (SELECT MAX(ID) FROM Changelog as ChangelogID ), 'License Added',new.ComputerFK);
...