MySQL 创建触发器语法

Question

DELIMITER // 
CREATE TRIGGER never_update_with_null
BEFORE UPDATE
ON `table_name`
FOR EACH ROW
BEGIN
    IF OLD.name IS NOT NULL AND NEW.name IS NULL THEN
        SET NEW.name = OLD.name;
    IF OLD.phone IS NOT NULL AND NEW.name IS NULL THEN
        SET NEW.phone = OLD.phone;
    IF OLD.place IS NOT NULL AND NEW.place IS NULL THEN
        SET NEW.place = OLD.place;
END; //
DELIMITER ;

我正在努力实现以下目标:如果某人UPDATE在name、phone 或place(列)中的值table_name 并且它的值(更新值)为 NULL 然后该值保持旧值(不更新)。也许更短一点:永远不要更新为 NULL。

上面的 SQL 没有创建触发器，但它也没有抛出任何错误(phpMyAdmin)。它缺少语法吗？已经在每次 OLD.column; 之后用 ENDIF; 尝试过 - 没有改进。谢谢你的解释。

Answer 1

尝试

DELIMITER // 
CREATE TRIGGER never_update_with_null
BEFORE UPDATE
ON `table_name`
FOR EACH ROW
BEGIN
    IF OLD.name IS NOT NULL AND NEW.name IS NULL THEN
        SET NEW.name = OLD.name;
    END IF;
    IF OLD.phone IS NOT NULL AND NEW.phone IS NULL THEN
        SET NEW.phone = OLD.phone;
    END IF;
    IF OLD.place IS NOT NULL AND NEW.place IS NULL THEN
        SET NEW.place = OLD.place;
    END IF;
END; //
DELIMITER ;