[SQL]: Database Triggers

SQL

10/02/2019

Database triggers are SQL statements that automatically runs when a specified table is changed. For example, it can be useful when updating a total.

Syntax

SQL

CREATE TRIGGER trigger_name
    trigger_time trigger_event ON table_name FOR EACH ROW
    BEGIN
    ...
    END;

trigger_time: BEFORE, AFTER
trigger_event: INSERT, UPDATE, DELETE

Example 1

SQL

CREATE TABLE users (
    username VARCHAR(100),
    age INT
);
INSERT INTO users VALUES ("Jack", 25);
INSERT INTO users VALUES ("YoungBoy", 13);

Create trigger

SQL

DELIMITER $$

CREATE TRIGGER check_age
    BEFORE INSERT ON users FOR EACH ROW
    BEGIN
        IF NEW.age < 18
        THEN
            SIGNAL SQLSTATE '45000'
                SET MESSAGE_TEXT = 'Must be 18 or older';
        END IF;
    END;
$$

DELIMITER ;

Try insert

SQL

INSERT INTO users VALUES("Robert", 19);
INSERT INTO users VALUES("Patrick", 17);

TEXT

mysql> INSERT INTO users VALUES("Robert", 19);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO users VALUES("Patrick", 17);
ERROR 1644 (45000): Must be 18 or older

Now it throws an error if you're trying to insert.

Syntax

SQL

NEW.age

NEW is a placeholder referring to the data about to be inserted.
Use OLD to access data that was deleted.

SQL

SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Must be 18 or older';

These syntax is for MySQL error (MySQL documentation)
45000: a numeric error code (MySQL specific)
SQLSTATE: 5 letter signal state code that can be recognized across different databases
MESSAGE_TEXT: description of the error

45000 is a state representing "unhandled user-defined exception"

SQL

DELIMITER $$
...
$$
DELIMITER ;

MySQL by default recognizes semi-colon as end of query.

DELIMITER \$\$ sets a new delimiter to \$\$ from ;

DELIMITER ; sets it back to semi-colon

You can set DELIMITER to however you want.

Example 2

Back in our Instagram clone database, the following code was allowed, although it shouldn't be. i.e. following oneself.

SQL

INSERT INTO follows(follower_id, followee_id) VALUES(3, 3);

Prevent following oneself on Instagram

SQL

DELIMITER //

CREATE TRIGGER prevent_follow_self
    BEFORE INSERT ON follows FOR EACH ROW
    BEGIN
        IF NEW.follower_id = NEW.followee_id
        THEN
            SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = "User can't follow oneself";
        END IF;
    END;
//
DELIMITER ;

Result

SQL

INSERT INTO follows(follower_id, followee_id) VALUES(3, 3);

TEXT

mysql> INSERT INTO follows(follower_id, followee_id) VALUES(3, 3);
ERROR 1644 (45000): User can't follow oneself

Example 3

Log unfollows

Create unfollows table (columns are same as follows table).

SQL

CREATE TABLE unfollows (
    follower_id INT NOT NULL,
    followee_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    FOREIGN KEY(follower_id) REFERENCES users(id),
    FOREIGN KEY(followee_id) REFERENCES users(id),
    PRIMARY KEY(follower_id, followee_id)
);

Create trigger

SQL

DELIMITER //
CREATE TRIGGER log_unfollow
    AFTER DELETE ON follows FOR EACH ROW
    BEGIN
        INSERT INTO unfollows(follower_id, followee_id)
        VALUES(OLD.follower_id, OLD.followee_id);
    END;
//
DELIMITER ;

OLD is a data that was deleted

Using SET (same trigger)

SQL

DELIMITER //
CREATE TRIGGER log_unfollow
    AFTER DELETE ON follows FOR EACH ROW
    BEGIN
        INSERT INTO unfollows
        SET follower_id = OLD.follower_id,
            followee_id = OLD.followee_id;
    END;
//
DELIMITER ;

Test

SQL

DELETE FROM follows WHERE follower_id=5 AND followee_id=7;
DELETE FROM follows WHERE follower_id=11 AND followee_id=3;
SELECT * FROM unfollows;

TEXT

+-------------+-------------+---------------------+
| follower_id | followee_id | created_at          |
+-------------+-------------+---------------------+
|           5 |           7 | 2019-10-03 05:07:15 |
|          11 |           3 | 2019-10-03 05:07:15 |
+-------------+-------------+---------------------+

Remove trigger

Show trigger

SQL

SHOW triggers;

DROP

SQL

DROP TRIGGER triggername;

Caveat

Triggers can make debugging difficult. When trigger is causing a bug, it is hard to capture it because triggers work behind the scene.

When you're using a multiple triggers, there's probably a way of doing it without using the trigger. It may be a better implementation without using the trigger.

WRITTEN BY

Ellis Min

Keeping a record

GitHub