chrismsawi chrismsawi - 6 months ago 12
MySQL Question

Error Code: 1054. Unknown column 'logical_delete_flag' in 'field_list'

Although there are many questions here similar to what I am encountering right now, the answers that I have tried are not working. Given the following table and trigger scripts, what would be the reason and resolution as to why the captioned topic is being encountered.

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 ;





Sequence of SQL Commands:

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;


Then, the error occurred.

Answer

You've missed using the OLD or NEW keyword around logical_delete_flag in your BEFORE UPDATE trigger. To access columns in rows affected by your trigger you need to use one of these keywords based on your scenario.

For you, you should use OLD around the column you're testing for TRUE/FALSE.

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 ;
Comments