SQL跟踪器记录表中的更改

debugcn 发表于 Dev

g 米哈尔·哈达克（Michal Hudak）

我有一张桌子叫table：

id    int(11)      AI    PK
name  varchar(255)

我将日志存储在 table_log

id     int(11)    AI    PK
action enum('insert', 'update', 'delete')
name   varchar(255)
ts     timestamp  current_timestam

目标：我想创建触发器以将日志存储到table_log

这是触发器：

CREATE TRIGGER `table_insert` AFTER INSERT ON `table`
    FOR EACH ROW
    BEGIN
      INSERT INTO `table_log` (id, action, name, ts)
      VALUES(NEW.id, 'insert', NEW.name, NOW());
    END;

CREATE TRIGGER `table_update` AFTER INSERT ON `table`
    FOR EACH ROW
    BEGIN
      INSERT INTO table_log (id, action, name, ts)
      VALUES(NEW.id, 'update', NEW.name, NOW());
    END;

CREATE TRIGGER `table_delete` AFTER INSERT ON `table`
    FOR EACH ROW
    BEGIN
      INSERT INTO `table_log` (id, action, name, ts)
      VALUES(OLD.id, 'delete', OLD.name, NOW());
    END;

我试图创建触发器，但是语法错误：

错误

SQL query:

CREATE TRIGGER `table_insert` AFTER INSERT ON  `table` 
FOR EACH
ROW 
BEGIN 
INSERT INTO  `table_log` ( id, 
ACTION , name, ts ) 
VALUES (

NEW.id,  'insert', NEW.name, 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 '' at line 5

于尔根

如果未设置除之外的其他定界符;，则该语句将在第一个结尾处结束，;并且触发器定义将不完整。您需要告诉MySQL，该语句应以您定义的定界符结尾。之后，您可以使用delimiter ;

delimiter |
CREATE TRIGGER `table_insert` AFTER INSERT ON  `table` 
FOR EACH ROW BEGIN 
  INSERT INTO  `table_log` ( id, ACTION , name, ts ) 
  VALUES (NEW.id,  'insert', NEW.name, NOW( ));
END
|
delimiter ;