Lesson 2 : Trigger

A trigger is a feature in most databases which serves as a background function that will execute on table’s changes. This execution will create changes to other table like update, delete or add a new row of information. Instead of having to navigate to other table to manually change its information or having to fire another set of query after the first one, trigger will automatically do the task for the database administrator or the programmer in the background without having to fire another query from the client program or manually navigate into the other table to do changes. For example when I add a new employee in EMPLOYEE table it will add a new count on POPULATION table according to gender.

Syntax 

DELIMITER $$ 

CREATE 

TRIGGER UpdatePopulation 

BEFORE/AFTER INSERT/UPDATE/DELETE ON `scheduling`.`<Table Name>` 

FOR EACH ROW 

BEGIN 

“DO SOMETHING HERE” 

END$$ 

DELIMITER ; 

Example 

DELIMITER $$ 

CREATE 

TRIGGER UpdatePopulation AFTER INSERT ON Employee FOR EACH ROW 

BEGIN 

Update Population set HeadCount = HeadCount+1 Where Gender = Gender.NEW; 

END$$ 

DELIMITER ; 

Note: BEFORE/AFTER INSERT/UPDATE/DELETE ON `scheduling`.`<Table Name>` 

This line indicates when the trigger will be executed, will it be BEFORE some changes are done to the first table or AFTER a change is done to the first table and will it be before an insert, update or delete happen on the first table or after an insert, update or delete happen on the first table. 

6 Combinations: 

BEFORE INSERT                        AFTER INSERT 

BEFORE UPDATE                      AFTER UPDATE 

BEFORE DELETE                       AFTER DELETE 

Activity: 

On your Elementary Grading System create a table Population and implement 3 triggers on it for AFTER INSERT, UPDATE and DELETE on the STUDENTS table. These triggers will update the total population of each classroom according to the students’ gender. 

Download PDF File