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:

Let’s start by creating 4 tables and see how a trigger affects them:

Now let’s define a trigger on table test1:

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?

We notice that:

Now let’s populate the databases and see how they look for now, before touching table test1:

dbt.PNG

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;

3) UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;

db2.PNG

We have 2 types of actions performed:

Some triggers are performed on a WHEN condition, delimiting the new and old data by using the NEW or OLD keyword:

 
39
Kudos
 
39
Kudos

Now read this

Intro to Database Systems - Part 11 to 13 : Integrity Constraints

Constraints are there to impose restrictions on what data is allowed in the database. The basic E/R model introduces us to 2 types of constraints: KEYS Multiplicities The relational model allows us to be more flexible. It imposes bounds... Continue →