First let’s start with the question, what are Triggers?  Triggers are database operations which are caused by data modification events performed in column(s) of a table. In simple words, Triggers are event driven operations. The events are nothing but data modification phenomenon such as insert, update, delete etc. It can consist of a single SQL statement or a block of SQL statements. Triggers are used to reduce manual intervention for tasks which are reparative in nature. Like auto update, data integrity etc.

Triggers are of two types;

• Row level triggers – Row level triggers executes once for each row of the table that is being modified by the triggering event.

• Statement level triggers – Statement triggers on the other hand, executes only once upon the execution of the entire triggering statement.

Again the above triggers can be executed ‘BEFORE’ any event occurs or ‘AFTER’.

In the ‘BEFORE’ triggers, the triggering action (action needs to be done like inserting a row) is only initiated but is not completed. Control then flows to the triggered action (check on the row in the same table). After the triggered action is complete, control returns to the triggering event, which then (if all went well) completes its job (inserts the row into the table).

In case of ‘AFTER’ triggers, first, control flows to the trigger event (update a row) upon completion only the control passes to the triggered action (insert a new row regarding the update).

To add to the list there are also a couple of more types of triggers; ‘INSTEAD OF’ and ‘CASCADING’. Former one is used perform something when the original trigger could not be completed. For example, on a particular UPDATE, you want to INSERT into a table but could not due to some error, then log this message to another table using INSTEAD OF trigger. On the other hand ‘CASCADING’ triggers are triggers fired by another trigger. TriggerB is fired by TriggerA on some update performed. Syntax for creating a trigger;

CREATE[REPLACE] TRIGGER <trigger_name>

[ENABLED|DISABLED] BEFORE|AFTER

INSERT|DELETE|UPDATE OF <column name> ON <table name>

REFERENCING OLD[row|table] AS <before processed row>

                   NEW[row|table] AS <after processed row>

FOR EACH ROW | STATEMENT WHEN (search_condition SQL statement)

(<triggered action to be performed >;);

Note the two semi-colons, first one is for the triggered action statement and the 2nd one is for the entire CREATE TRIGGER statement.

Our Random Articles

More Links