mysql - 错误代码 : 1054. 'logical_delete_flag' 中的未知列 'field_list'

Question

虽然这里有很多类似我现在遇到的问题，但我试过的答案都不起作用。给定下表和触发脚本，遇到标题主题的原因和解决方案是什么。

CREATE TABLE todolist (
    sequence_number INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
    to_do_item VARCHAR(45) NOT NULL,
    done_status BOOLEAN DEFAULT FALSE,
    active_flag BOOLEAN DEFAULT TRUE,
    last_action_code VARCHAR(45) NOT NULL,
    create_pid VARCHAR(45),
    create_datetime TIMESTAMP,
    read_pid VARCHAR(45),
    read_datetime TIMESTAMP,
    update_pid VARCHAR(45),
    update_timestamp TIMESTAMP,
    delete_pid VARCHAR(45),
    delete_timestamp TIMESTAMP,
    logical_delete_flag BOOLEAN DEFAULT FALSE
);

---

DELIMITER ;;
CREATE TRIGGER todolist_trigger_on_record_insert
    BEFORE INSERT ON todolist
    FOR EACH ROW
        BEGIN
            SET NEW.create_datetime = NOW(),
                NEW.create_pid = 'todolist_trigger_on_record_insert',
                NEW.last_action_code = 'INSERT';
        END;;
DELIMITER ;

---

DELIMITER ;;
CREATE TRIGGER todolist_trigger_on_record_update
    BEFORE UPDATE ON todolist
    FOR EACH ROW
        BEGIN
            IF (logical_delete_flag IS TRUE) THEN
                SET NEW.delete_timestamp = NOW(),
                    NEW.delete_pid = 'todolist_trigger_on_record_logical_delete',
                    NEW.last_action_code = 'LOGICAL_DELETE';
            ELSE
                SET NEW.update_timestamp = NOW(),
                    NEW.update_pid = 'todolist_trigger_on_record_update',
                    NEW.last_action_code = 'UPDATE';
            END IF;
        END;;
DELIMITER ;

---

SQL 命令序列:

INSERT INTO todolist(to_do_item) VALUE ('Prepare for the assignment');
UPDATE todolist SET to_do_item = 'Check on how to start on the assignment' WHERE sequence_number = 1;

然后，错误发生了。

Answer 1

您在 BEFORE UPDATE 触发器中没有在 logical_delete_flag 周围使用 OLD 或 NEW 关键字。要访问受触发器影响的行中的列，您需要根据您的情况使用这些关键字之一。

对于您来说，您应该在要测试 TRUE/FALSE 的列周围使用 OLD。

DELIMITER ;;
CREATE TRIGGER todolist_trigger_on_record_update
    BEFORE UPDATE ON todolist
    FOR EACH ROW
        BEGIN
            IF (OLD.logical_delete_flag IS TRUE) THEN
                SET NEW.delete_timestamp = NOW(),
                    NEW.delete_pid = 'todolist_trigger_on_record_logical_delete',
                    NEW.last_action_code = 'LOGICAL_DELETE';
            ELSE
                SET NEW.update_timestamp = NOW(),
                    NEW.update_pid = 'todolist_trigger_on_record_update',
                    NEW.last_action_code = 'UPDATE';
            END IF;
        END;;
DELIMITER ;