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

Question

我似乎无法创建触发器。我已经尝试过以下两种更新方式。我不断收到插入语句的语法错误。过去 4 小时我搜索了论坛和网络搜索，没有任何变化。还有很多代码，它基本上是重复的。任何帮助将不胜感激。谢谢。我正在运行 MySQL 5.0 并通过 phpMyAdmin 2.8.2.4 作为管理员/根进行访问。

---

TRY 1（所有字段和名称上有和没有 qoutes）

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;

---

ERROR

 #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 ;

---

ERROR

    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 和modified = NOW() 语句而不做任何更改。

Answer 1

必须删除 Delimiter、Begin 和 End 语句。

---

回答

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());