March 19, 2015

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?

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:

Here is how the trigger updates the data of the table before the insertion:

1) INSERT INTO test2 SET a2 = NEW.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

Kudos