How do I create an SQL trigger that has an if-condition?

I’m taking an introductory databases course in college right now, and we’re tasked with creating a basic Flickr clone web app. I’ve created all my tables, but I can’t seem to properly create triggers. My partner has not been able to figure out this problem either. One condition for this app is that the owner of an album cannot change. Currently, our trigger looks like this:

CREATE TRIGGER ownerChange
    BEFORE UPDATE ON Albums
    FOR EACH ROW
    BEGIN
        -- owner is a column of the Albums table
        IF (NEW.owner <> OLD.owner) THEN
            -- Our textbook mentions using "RAISE_APPLICATION_ERROR",
            -- but the darn book is 20 years old, and Googling suggests
            -- using SIGNAL instead.
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Changing album owner is not allowed.'
        END IF
    END

If I drop the database and try to cleanly source the schema containing this trigger, the tables are all created just fine, but I receive

ERROR 1064 (42000): 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 'END IF
    END' at line 75

I’m using MySQL version 8.0.23.

Answer

Your are missing two semicolons

like seen in the example.

CREATE TABLE Albums (
  `owner` INTEGER
);
CREATE TRIGGER ownerChange
    BEFORE UPDATE ON Albums
    FOR EACH ROW
    BEGIN
        -- owner is a column of the Albums table
        IF (NEW.owner <> OLD.owner) THEN
            -- Our textbook mentions using "RAISE_APPLICATION_ERROR",
            -- but the darn book is 20 years old, and Googling suggests
            -- using SIGNAL instead.
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Changing album owner is not allowed.';
        END IF;
    END
INSERT INTO Albums VALUES (1);
UPDATE Albums SET owner = 2 WHERE owner = 1;
Changing album owner is not allowed.
SELECT * FROM Albums;
| owner |
| ----: |
|     1 |

db<>fiddle here

outside of mysql Workbench, you need also DELIMITER

DELIMITER $$
CREATE TRIGGER ownerChange
    BEFORE UPDATE ON Albums
    FOR EACH ROW
    BEGIN
        -- owner is a column of the Albums table
        IF (NEW.owner <> OLD.owner) THEN
            -- Our textbook mentions using "RAISE_APPLICATION_ERROR",
            -- but the darn book is 20 years old, and Googling suggests
            -- using SIGNAL instead.
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Changing album owner is not allowed.';
        END IF;
    END$$
DELIMITER ;

Leave a Reply

Your email address will not be published. Required fields are marked *