MySQL before delete trigger get other table value for condition

hirohito 0
Viewed 36

I want to make a trigger to permanently delete transaction IF latest status is closed,

I tried my trigger code below:

CREATE TRIGGER `prevent_delete_data` BEFORE DELETE ON `table_transaction` FOR EACH ROW
BEGIN
    DECLARE latest_track_status varchar(255);
    SELECT status INTO latest_track_status FROM table_transaction_track_status WHERE transaction_id = OLD.id ORDER BY created_at DESC LIMIT 1;
        IF (latest_track_status != closed) THEN
            SIGNAL SQLSTATE "45000"
            SET MESSAGE_TEXT = "You can\'t delete data if latest transaction status IS NOT closed";
        END IF;
END

but MySQl says #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 '' at line 3

how did I solve that?

thanks

MySQL version 8.0.30

triggers mysql
1 Answers

my bad where I forgot to add DELIMITER and closed inside IF condition must be string quoted 'closed' thanks to @P.Salmon

EDIT: this is the result I have done

DELIMITER $$
    CREATE TRIGGER `prevent_delete_data` BEFORE DELETE ON `table_transaction` FOR EACH ROW
    BEGIN
        DECLARE latest_track_status varchar(255);
        SELECT status INTO latest_track_status FROM table_transaction_track_status WHERE transaction_id = OLD.id ORDER BY created_at DESC LIMIT 1;
            IF (latest_track_status != 'closed') THEN
                SIGNAL SQLSTATE "45000"
                SET MESSAGE_TEXT = "You can\'t delete data if latest transaction status IS NOT closed";
            END IF;
    END
$$
hirohito0
Related