Triggers

Trigger is a Named Sub-Program which gets fired whenever an action occur in the table. It can be DML’s on the associated table or view, DDL issued in a schema or database or an action in the database like startup, shutdown, logon, logoff, etc. Triggering Events in case of DML’s can be an INSERT, UPDATE or DELETE. Triggers are fired no matter which user is connected or application being used. Database implicitly executes these triggers whenever the action event occurs. User cannot execute them explicitly.

Based on the number of times the trigger action to be executed user can define the trigger. There are 2 types of DML triggers. They are –

             1.       Statement level Trigger and  

             2.       Row level Triggers.

Statement Level triggers are fired once regardless of the number of rows the triggering statement affects the associated table.

Row Level triggers are fired for every record the DML is affecting the associated table. In other words, they are fired for every row affected by the triggering statement.

Basic difference between statement level and row level trigger is -

Statement level triggers are fired regardless of the number of rows the triggering statement is affecting. They are fired once even if there are no affected rows. But, Row level triggers are fired only when the rows in the associated table are affected.  

We can specify the trigger timing when the triggering action to be executed either BEFORE or AFTER the triggering statement. We can use BEFORE or AFTER in both the statement level Triggers and Row level Triggers.  

We can create four types of triggers with the combination of the type of trigger and the timing. They are -

Before Statement Level – These are fired Before the triggering statement is fired.

After Statement Level – These are fired AFTER the triggering Statement is fired.

Before Row Level – Before the triggering statement is affecting each row in the associated table. 

After Row Level - Before the triggering statement is affecting each row in the associated table.

Trigger execution Sequence –

1. All Before Statement Level Triggers.
2. All Before Row Level Triggers.
3. All After Row Level Triggers.
4. All After Statement Level Triggers.

We can have multiple DML triggers on a table.

The PL/SQL block of a trigger cannot contain transaction control SQL statements (COMMIT, ROLLBACK, SAVEPOINT, and SET CONSTRAINT) if the block is executed within the same transaction.

There are 3 basic parts in the trigger. 

1.       Triggering Statement or Event

2.       Trigger Restriction

3.       Trigger Action

Syntax for Creating Trigger –

CREATE [OR REPLACE ] TRIGGER trigger_name

 {BEFORE | AFTER | INSTEAD OF }                   à Triggering Event Timing

 {INSERT [OR] | UPDATE [OR] | DELETE}  à Triggering Event

 [OF col_name]

 ON table_name

 [REFERENCING OLD AS o NEW AS n]

 [FOR EACH ROW]

 WHEN (condition)                         à Trigger Restriction

 BEGIN

   --- sql statements                 à Trigger Action

 END; 

CREATE [OR REPLACE ] TRIGGER trigger_name  à Creates trigger with the given name if it is not there or Overwrites the existing one.

{BEFORE | AFTER | INSTEAD OF } à This clause indicates the triggering time to fire.

{INSERT [OR] | UPDATE [OR] | DELETE} à indicates the triggering Event.

REFERENCING à This clause is used to reference Old and New Values being changed. We refer the old and new values as - :NEW.Column_Name and :OLD.Column_Name respectively.

[FOR EACH ROW] à Type of the trigger. If this is not specified, it will be treated as a STATEMENT LEVEL Trigger

WHEN à This clause is used to check the condition for rows. Triggers are fired only when the row satisfies the condition. In otherwords, trigger is fired only when the condition evaluates to TRUE. Trigger event won’t be executed If it is FALSE or UNKNOWN. This Clause is valid for only row level triggers.  

Restrictions on Trigger Conditions Trigger conditions are subject to the following restrictions:

·         If you specify this clause for a DML event trigger, then you must also specify FOR EACH ROW. Oracle Database evaluates this condition for each row affected by the triggering statement.

·         You cannot specify trigger conditions for INSTEAD OF trigger statements.

You can reference object columns or their attributes, or varray, nested table, or LOB columns. You cannot invoke PL/SQL functions or methods in the trigger condition.

Data Dictionary view to view triggers is – USER_TRIGGERS. We can even query USER_SOURCES for getting information about the triggers.

 The structure of USER_TRIGGERS is –

Name	Type
TRIGGER_NAME	VARCHAR2(128)
TRIGGER_TYPE	VARCHAR2(16)
TRIGGERING_EVENT	VARCHAR2(246)
TABLE_OWNER	VARCHAR2(128)
BASE_OBJECT_TYPE	VARCHAR2(18)
TABLE_NAME	VARCHAR2(128)
COLUMN_NAME	VARCHAR2(4000)
REFERENCING_NAMES	VARCHAR2(422)
WHEN_CLAUSE	VARCHAR2(4000)
STATUS	VARCHAR2(8)
DESCRIPTION	VARCHAR2(4000)
ACTION_TYPE	VARCHAR2(11)
TRIGGER_BODY	LONG
CROSSEDITION	VARCHAR2(7)
BEFORE_STATEMENT	VARCHAR2(3)
BEFORE_ROW	VARCHAR2(3)
AFTER_ROW	VARCHAR2(3)
AFTER_STATEMENT	VARCHAR2(3)
INSTEAD_OF_ROW	VARCHAR2(3)
FIRE_ONCE	VARCHAR2(3)
APPLY_SERVER_ONLY	VARCHAR2(3)