Term: TRIGGER

Definition:
An Oracle trigger is a database object that is attached to a table. It is similar in many ways to a stored procedure, except that a trigger is run or fired in response to an INSERT, UPDATE or DELETE action. You decide what actions or events are to take place when the trigger is fired by placing code in the trigger. This may be inline code (code stored within the trigger itself) or stored procedures which are outside the trigger, but called by code from within the trigger.

The triggered events can either be a DML event, a DDL event or a database event. (DDL event and database event triggers are also called "system triggers").

Example Uses Of Triggers
Triggers are an extremely useful and flexible feature and are used in many different ways. A trigger could be used, for example, to send email when an event takes place (like a product order being placed) or to send an alert to a pager if a danger condition occurs (like an abnormally high temperature in a server room). They also quite commonly used to initiate downstream events like order processing, data-dependent backups, or populating a newly-created user account with default information. Triggers are so useful that it is rare to find any non-trivial database application that doesn't use them.

The following database-specific events can also be caught and used to fire a trigger:

Servererror
Logon
Logoff
Startup
Shutdown
Suspend

For Oracle triggers to work, dbmsstdx.sql must have been called. Normally, catalog.sql is run after the creation of a database. The catalog.sql in turn calls dbmsstdx.sql.

The "inserted" and "deleted" Tables
Triggers make use of a pair of special reserved tables named "inserted" and "deleted". The "inserted" table contains the data referenced in an INSERT before it is actually committed to the database. The "deleted" table contains the data in the table referenced in the DELETE statement before it is actually removed from the database.

Both tables are used when an UPDATE statement is issued. The new data referenced in the UPDATE statement is contained in "inserted" and the data that is being updated is placed in "deleted".

Basic Trigger Syntax:

CREATE OR REPLACE TRIGGER <TRIGGER_NAME>

  before INSERT or UPDATE

ON <table_name>

  for each row

DECLARE

  <VARIABLE DECLARATIONS>

BEGIN

    <CODE>

EXCEPTION

    <EXCEPTION HANDLERS>

END <TRIGGER_NAME>

/

Trigger Examples:

This example records the user and the time whenever a user logs onto the database. It uses the following table:

CREATE TABLE logon_tbl (

who VARCHAR2(30), 

when DATE

);

The actual trigger code is as follows:

CREATE OR REPLACE TRIGGER trg_logon_db

  after logon on database

BEGIN

  INSERT INTO logon_tbl (who, when) VALUES (user, sysdate);

END;

/

This example is based on the following two tables:

CREATE TABLE T4 (a INTEGER, b CHAR(10));

CREATE TABLE T5 (c CHAR(10), d INTEGER);

This creates a trigger that may insert a tuple into table Table_5 when a tuple is inserted into table Table_4. Specifically, the trigger checks whether the new tuple has a first component 10 or less, and if so inserts the reverse tuple into Table_5:

CREATE TRIGGER trig_1

AFTER INSERT ON Tbale_4

REFERENCING NEW AS newRow

FOR EACH ROW

WHEN (newRow.a <= 10)

BEGIN

    INSERT INTO Table_5 VALUES(:newRow.b, :newRow.a);

END trig_1;

A statement level trigger with multiple actions:

DECLARE

 vMsg VARCHAR2(30) := 'Statement Level Trigger Fired';

BEGIN

  IF INSERTING THEN

    dbms_output.put_line(vMsg || ' When Inserting');

  ELSIF UPDATING THEN

    dbms_output.put_line(vMsg || ' When Updating');

  ELSIF DELETING THEN

    dbms_output.put_line(vMsg || ' When Deleting');

  END IF;

END statement_level;

/

Cascading Triggers
One danger of using triggers is the condition known as "cascading". This occurs when the actions of one trigger cause another trigger to fire, which in turn causes the original trigger to fire again. When the original trigger is re-fired it causes the second trigger to fire again, and the cycle continues, sometimes indefinitely.

If not caught and handled properly in code this cascading condition (sometimes referred to as a trigger war) will nearly always cause problems (some of which may be fatal to the database). For example, storage space may become exhausted, endless streams of emails or alerts may be sent out, an order may never finish processing, or multiple orders may be recorded instead of one. Always test to make sure that the triggers you create do not cause a cascading effect. If necessary, add code flags that lock out repeating trigger actions where appropriate, for example, when a specific condition has been met.