Triggers

Triggers are a special construct similar to Procedures. Triggers became famous due to their use in PL/SQL - the programming language that comes with the ORACLE DBMS. Procedures are executed when they are invoked from the outside by a program or another procedure while a Trigger is executed whenever the 'triggering' event set to start it takes place. In the ORACLE DBMS, The triggering event could be an INSERT, DELETE, or UPDATE SQL command. The timing of the trigger can either be BEFORE or AFTER one of these commands. Row level triggers take place once for each row affected by the triggering statement while statement level triggers fire just once for per UPDATE query.

Figure 8b: Example of a simple Oracle row level trigger

The Trigger above happens every time an UPDATE statement is encountered for the ORDERS table. The Trigger is executed first. It is given the name row_level.

'FOR EACH ROW ' indicates that the Trigger is executed once for every updated row

The DECLARE portion contains variable declarations. Variable declarations are similar to 'Tom is a man” except that new strings, numbers and so on are given names. Here, a string of characters is given the name 'vMsg' and set to equal 'Row Level Trigger Fired'.

The BEGIN statement is the start of the program that will be executed line by line. The words 'Row Level Trigger Fired' is written either into a log file or on the console of the DBMS.

This is the only action that takes place in the trigger, the 'END row_level' signals the end of the trigger.

Below this are regular SQL statements. Can you guess when the trigger will be fired?