MySQL数据库中跟踪更改的触发器

Question

我似乎无法创建触发器。我已经尝试了以下两种更新的方式。插入语句出现了语法错误。我已经在论坛和网络搜索中搜索了过去4个小时，但没有任何改变。此代码还有更多内容，基本上是重复的。谢谢您的帮助。我正在运行MySQL 5.0并以管理员/根用户身份通过phpMyAdmin 2.8.2.4访问。

---

尝试1（所有字段和名称都加引号和不加引号）

CREATE TRIGGER insert_classes
AFTER insert ON Classes
FOR EACH ROW
BEGIN
    insert into insert_tracking_classes (classID, Title, classDesc, Category, isEvent, picLeft, picTop, picRight, picBottom, prnColor, modified)
    values(NEW.classID, NEW.Title, NEW.classDesc, NEW.Category, NEW.isEvent, NEW.picLeft, NEW.picTop, NEW.picRight, NEW.picBottom, NEW.prnColor, NOW());
END;

CREATE TRIGGER insert_classes
AFTER insert ON Classes
FOR EACH ROW
BEGIN
insert into insert_tracking_classes
        set classID = NEW.classID,
        Title = NEW.Title,
        classDesc = NEW.classDesc,
        Category = NEW.Category,
        isEvent = NEW.isEvent,
        picLeft = NEW.picLeft,
        picTop = NEW.picTop,
        picRight = NEW.picRight,
        picBottom = NEW.picBottom,
        prnColor = NEW.prnColor,
        modified = NOW(); 
END;

错误

 #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line * (at the end of the insert/values statement)

---

TRY 2

 DELIMITER $$
 CREATE TRIGGER insert_classes AFTER insert ON Classes
 FOR EACH ROW BEGIN
insert into insert_tracking_classes (classID, Title, classDesc, Category, isEvent, picLeft, picTop, picRight, picBottom, prnColor, modified)
values(NEW.classID, NEW.Title, NEW.classDesc, NEW.Category, NEW.isEvent, NEW.picLeft, NEW.picTop, NEW.picRight, NEW.picBottom, NEW.prnColor, NOW());
 END$$
 DELIMITER ;

错误

    SQL query:

    DELIMITER $$ CREATE TRIGGER insert_classes AFTER INSERT ON Classes
    FOR EACH
    ROW BEGIN
    INSERT INTO insert_tracking_classes( classID, Title, classDesc, Category, isEvent, picLeft, picTop, picRight, picBottom, prnColor, modified )
    VALUES (
-->     NEW.classID, NEW.Title, NEW.classDesc, NEW.Category, NEW.isEvent, NEW.picLeft, NEW.picTop, NEW.picRight, NEW.picBottom, NEW.prnColor, NOW( )
    );

    MySQL said: Documentation
    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER $$
         CREATE TRIGGER insert_classes AFTER insert ON Classes
         F' at line 1 

我的表格

CREATE TABLE insert_tracking_classes (  --- Same table layout for Classes table minus Modified time and tracking_id
  tracking_id int(11) NOT NULL AUTO_INCREMENT,
  classID int(11) NOT NULL,
  Title varchar(48) NOT NULL,
  classDesc text,
  Category varchar(128) default NULL,
  isEvent int(8) default NULL,
  picLeft int(8) default NULL,
  picTop int(8) default NULL,
  picRight int(8) default NULL,
  picBottom int(8) default NULL,
  prnColor varchar(4) default NULL,
  modified datetime NOT NULL,
  PRIMARY KEY (tracking_id)
);

更新：尝试使用静态值，并删除if语句以及修改= NOW()语句，但没有改变。

- James Williams

---

你尝试过逐个创建触发器吗？如果是的话，能否详细说明错误出现在哪里以及是什么错误？ - Telmo Marques

是的，我有同样的语法错误。 - James Williams

错误只说“语法错误”？提供错误的完整描述可能会有所帮助。 - Telmo Marques

Answer 1

必须删除分隔符、开始语句和结束语句。

---

答案

DROP TRIGGER IF EXISTS insert_classes;
CREATE TRIGGER insert_classes AFTER insert ON Classes
FOR EACH ROW
insert into tracking_classes (command, classID, Title, classDesc, Category, isEvent, picLeft, picTop, picRight, picBottom, prnColor, modified)
values('insert', NEW.classID, NEW.Title, NEW.Desc, NEW.Category, NEW.isEvent, NEW.picLeft, NEW.picTop, NEW.picRight, NEW.picBottom, NEW.prnColor, NOW());

- James Williams