TRIGGERS

A trigger is a stored program which will get executed when an event occurs on a table which is nothing but an insert or update or delete statement. You cannot call a trigger like a stored procedure or a function. Triggers cannot pass any arguments to triggers.

Following are different types of triggers on a table.

  * Insert Trigger   (Before statement, Before Row, After Row, After Statement)

  * Update Trigger (Before statement, Before Row, After Row, After Statement)

  * Delete Trigger   (Before statement, Before Row, After Row, After Statement)

Following picture demonstrate the events and triggers associated to a table.

Triggers can be used to maintain the referential integrity, used to implement the business rules, capture the audit information like who and when record got created, who updated the record etc etc.

In statement level trigger you will approach either you want to proceed with the statement or not. In row level trigger you will validate the data incoming and make necessary data modifications with in the trigger based on the business rules.

Sequence of Trigger execution
When you issue an insert statement to the table the database engine checks whether that table has the trigger, first it checks whether the table has Before Insert Statement level trigger, if so execute the trigger then it checks if there is a Before Insert Row level trigger, if so execute the trigger then it checks if there is a After Insert Row level trigger, if so execute the trigger then it checks is there another row to be inserted, if so it triggers the Before Insert Row level trigger. After all the rows got inserted then it executes After Insert Statement level trigger.

Syntax
Create trigger trigger_name
before insert
for each row
begin
pl/sql block
end;

Example
create trigger tr_student before insert
for each row
begin
update course set no_of_students = no_of_students + 1
where course_id = :new.course_id;
end;