Can't Create MySQL TRIGGER when i use IS NOT NULL

Question

CREATE TRIGGER b_I_O AFTER UPDATE ON book
FOR EACH ROW
BEGIN
IF (OLD.status IS NOT NULL AND NEW.status IS NOT NULL AND NEW.status != OLD.status ) THEN
    INSERT INTO book_in_out (astatus, bid, time) VALUES(NEW.status, OLD.id, NOW());
END IF;
END;

First post here after hours of searching.

I'm trying to insert into book_in_out using trigger after updating booking table, I was able to do this but if I update bookName the trigger will try to run.

I only want the trigger to run only when status column changes. default value for status column is NULL

Below is the error i keep getting:

#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 5

Answer 1

You may try this

delimiter //
CREATE TRIGGER b_I_O AFTER UPDATE ON book
FOR EACH ROW
BEGIN
    IF (OLD.status IS NOT NULL AND NEW.status IS NOT NULL AND NEW.status != OLD.status ) THEN
        INSERT INTO book_in_out (`astatus`, `bid`, `time`) VALUES(NEW.status, OLD.id, NOW());
    END IF;
END;//
delimiter ;