mysql - 创建Mysql触发器的语法

Question

CREATE TRIGGER expiryset  ON test FOR INSERT
BEGIN 
DECLARE exp TIMESTAMP;
DECLARE name TEXT;
SET @name := (SELECT name from test ORDER BY created_at DESC limit 1);
SET @exp := (SELECT TIMESTAMPADD(WEEK,2,(CURRENT_TIMESTAMP)));
UPDATE test SET expiry = @exp WHERE name= @name; 
END;

上述触发器有效，但仅在执行时我无法保存它。语法有什么问题

Answer 1

您必须将 DELIMITER 设置为与默认 ; 不同的值，才能将整个代码放在一起。

DELIMITER $$

CREATE TRIGGER expiryset  AFTER INSERT ON test FOR EACH ROW
BEGIN 
DECLARE exp TIMESTAMP;
DECLARE name TEXT;
SET @name := (SELECT name from test ORDER BY created_at DESC limit 1);
SET @exp := (SELECT TIMESTAMPADD(WEEK,2,(CURRENT_TIMESTAMP)));
UPDATE test SET expiry = @exp WHERE name= @name; 
END$$

DELIMITER ;