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 6 & 7 : Basic SQL

This part won’t cover the notions of passing from relational algebra to SQL since this has already been covered in the previous post. In this section, we will start by introducing a main overview of a basic SQL query. Then, we structure... Continue →