Intro to Database Systems - Part 14 to 16 : Triggers
A trigger is a procedure that executes automatically as soon as specified changes occur in the DBMS.
A trigger has 3 parts:
- an event : at what type of change the procedure should happen? Usually it happens before/after/insteadOf an insert/update/delete.
- an action : what happens if the trigger runs? (example: add student to scholarshipStudList)
- a condition: under which condition the procedure gets executed once the event triggered? In other words, when does the event gets executed? (example: add student to scholarshipStudList only when studGPA > 3.6).
Let’s start by creating 4 tables and see how a trigger affects them:
- CREATE TABLE test1(a1 INT);
- CREATE TABLE test2(a2 INT);
- CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
- CREATE TABLE test4( a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b4 INT DEFAULT 0 );
Now let’s define a trigger on table test1:
- CREATE TRIGGER testReference BEFORE INSERT ON test1 FOR EACH ROW BEGIN INSERT INTO test2 SET a2 = NEW.a1; DELETE FROM test3 WHERE a3 = NEW.a1; UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1; END;
We notice that we have two parts.
1) An event part: at what type of change should the event happen?
- BEFORE INSERT ON test1
2) An action part: what happens if the trigger runs?
- BEGIN INSERT INTO test2 SET a2 = NEW.a1; DELETE FROM test3 WHERE a3 = NEW.a1; UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1; END;
We notice that:
- the action part is surrounded by the keywords BEGIN and END : we are using them to encapsulate more than one action.
- there is no condition part. This parameter is optional.
Now let’s populate the databases and see how they look for now, before touching table test1:
- INSERT INTO test3 (a3) VALUES (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL);
- INSERT INTO test4 (a4) VALUES (0), (0), (0), (0), (0), (0), (0), (0), (0), (0);
Let’s modify table test1 and see how the tables are looking like now:
- INSERT INTO test1 (a1) VALUES (1), (3), (1), (7), (1), (8), (4), (4);
Here is how the trigger updates the data of the table before the insertion:
1) INSERT INTO test2 SET a2 = NEW.a1;
- a2 became a copy of the NEW table a1
2) DELETE FROM test3 WHERE a3 = NEW.a1;
- we’ve deleted all the values from a3 that are the same as values from the new table a1. All the different values remain. (1 is present in a1, we delete it; 2 isn’t present, we keep it; 3 is present, as well as 4 and 7 and 8. We delete them)
3) UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
- we are updating b4 every time (for each row) that we find a1 = a4. (For 1, a1 = a4 three times: increment b4 three times; For 2, there is no value 2 in a1: don’t increment b4; For 3, we find it once in a1: increment once; For 4, we find it twice: increment twice.)
We have 2 types of actions performed:
- FOR EACH STATEMENT : triggered once the entire statement is executed, independently of the number of rows affected.
- FOR EACH ROW : triggered when rows of a table are modified, it is fired as many times the rows are modified.
Some triggers are performed on a WHEN condition, delimiting the new and old data by using the NEW or OLD keyword:
- CREATE TRIGGER ratingIncrease AFTER UPDATE OF rating on Skaters REFERENCING OLD AS o NEW AS n FOR EACH ROW (WHEN (n.rating > 1 + o.rating) UPDATE Skaters SET rating = 1 + o.rating WHERE sid = n.sid