mysql - 在 MYSQL 的 INSERT 期间创建触发器显示错误

Question

创建表:

CREATE TABLE Persons (
    ID int NOT NULL AUTO_INCREMENT,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Birth_Date date,
    Review_Date date,
    PRIMARY KEY (ID)
);

在向该表中插入数据时，我想添加一个触发器，该触发器将更新 Review_Date 列的值为从出生日期算起 1 年。下面是我编写的触发器，它显示错误。

CREATE TRIGGER tri_Test_Insert_Trigger 
AFTER INSERT 
ON persons
FOR Each row
BEGIN
    UPDATE persons
    SET review_date = dateadd(year,1,birth_date)
END
GO 

Error Code: 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 'END GO' at line 8 0.000 sec

Answer 1

触发器中需要更新三件事:

一个。需要在 UPDATE sql
末尾添加 ;b.删除触发器末尾的 GO 并改用 DELIMITER
C。 dateadd需要改为date_add

 DELIMITER $$
    CREATE TRIGGER tri_Test_Insert_Trigger 
    AFTER INSERT 
    ON persons
    FOR Each row
    BEGIN
        UPDATE persons
        SET review_date = date_add(year,1,birth_date); -- add ; here
    END$$
  DELIMITER ; -- remove GO and use DELIMITER instead