6.3. Insert Row Trigger Syntax

The Insert Row trigger has the following syntax.

CREATE OR REPLACE TRIGGER trigger_name
AFTER|BEFORE INSERT ON table_name
FOR EACH ROW
[WHEN (Boolean expression)]
DECLARE
    Local declarations
BEGIN
    Trigger Body written PL/SQL
END;

TRIGGER_NAME

Use trigger names that identify the table name and trigger type. A PL/SQL run time error will generate a PL/SQL error message and reference the trigger name and line number. The following Oracle error indicates that Line 5 in the AFTER-INSERT row trigger, on the STUDENTS table, has a divide-by-zero error.

ORA-01476: divisor is equal to zero
ORA-06512: at "SCOTT.STUDENTS_AIR", line 5
ORA-04088: error during execution of trigger
  'SCOTT.STUDENTS_AIR'

The counting of line numbers begins at the keyword DECLARE. If there is no DECLARE section, then the BEGIN statement is line number 1. Trigger names are found in the column TRIGGER_NAME of USER_TRIGGERS. Trigger names used in this text are derived from the table name, trigger_type, and triggering event. The syntax is:

trigger_name = table_name_[A|B] [I|U|D] [R|S]

Examples of trigger names:

AFTER|BEFORE INSERT ON TABLE_NAME

This clause tells Oracle when to execute the trigger. It can be BEFORE or AFTER Oracle's integrity constraint checks. You can designate a BEFORE or AFTER trigger to fire on multiple statement types, examples are:

BEFORE INSERT OR UPDATE on table_name
BEFORE INSERT OR UPDATE OR DELETE on table_name
AFTER INSERT OR DELETE on table_name
					

Case logic in the trigger body can isolate a section of code to a particular SQL statement. The Oracle package DBMS_STANDARD includes four functions that are intended for this purpose:

PACKAGE DBMS_STANDARD IS
    FUNCTION inserting RETURN BOOLEAN;
    FUNCTION updating RETURN BOOLEAN;
    FUNCTION updating (colnam VARCHAR2) RETURN BOOLEAN;
    FUNCTION deleting RETURN BOOLEAN;
    etc,
END DBMS_STANDARD;

Use of the aforementioned functions in any context other than a trigger body evaluates to NULL. You do not use the package name when referencing these functions. The following illustrates a trigger using a CASE construct and the DBMS_STANDARD functions.

CREATE OR REPLACE TRIGGER temp_aiur
AFTER INSERT OR UPDATE ON TEMP
FOR EACH ROW
BEGIN
    CASE
    WHEN inserting THEN
        dbms_output.put_line
            ('executing temp_aiur - insert'),
    WHEN updating THEN
        dbms_output.put_line
            ('executing temp_aiur - update'),
    END CASE;
END;

An UPDATE ROW trigger can specify the columns being updated as a condition for firing the trigger. The syntax is:

OF column_name [,column_name]

For example, the following trigger fires only when columns M or P are included in the UPDATE statement.

CREATE OR REPLACE TRIGGER temp_aur
AFTER INSERT OR UPDATE OF M, P ON TEMP
FOR EACH ROW
BEGIN
    dbms_output.put_line
       ('after insert or update of m, p'),
END;

DECLARE

As with any PL/SQL block, this is not necessary if there are no local declarations.

WHEN (BOOLEAN EXPRESSION)

This clause is optional and can be used to filter the condition for when you want to fire the trigger.

Why would you use this? The WHEN option can be used on any ROW level trigger. Consider updating a table with many rows. A row level trigger can impact performance. If a million rows are updated, one million executions of the trigger will be noticed. The performance impact from the row trigger could be reason enough to remove it from the application.

However, there may be special circumstances in which the trigger is needed. The WHEN clause provides an opportunity to control the trigger execution—to have it fire on short transactions and not on massive updates.

Within the parentheses of the WHEN clause, the reference to column values is with the following syntax:

To illustrate, the following is an AFTER INSERT row trigger that fires only when the column value for N is equal to 0.

CREATE OR REPLACE TRIGGER temp_air
AFTER INSERT ON TEMP
FOR EACH ROW
WHEN (NEW.N = 0)
BEGIN
    dbms_output.put_line('executing temp_air'),
END;

The aforementioned trigger will fire with this next SQL statement:

INSERT INTO TEMP VALUES (0);

The aforementioned trigger will not fire with the following two statements:

INSERT INTO TEMP VALUES (2);
INSERT INTO TEMP VALUES (NULL);

The value of NEW.column is either the value included in the SQL statement, NULL, or the value from the column DEFAULT. Consider the TEMP TABLE:

CREATE TABLE temp (N NUMBER DEFAULT 0, M NUMBER);

The aforementioned trigger will fire for the following SQL statement. This is because NEW.N is equal to the DEFAULT, which is 0. The trigger fires only when NEW.N is zero.

INSERT INTO TEMP (M) VALUES (3);

You can code OLD.column in an INSERT trigger. It is not a syntax error and evaluates to NULL. The following illustrates a trigger with a triggering event of INSERT OR UPDATE. The WHEN clause stipulates that the trigger fires on either of the following conditons:

CREATE OR REPLACE TRIGGER temp_biur
BEFORE INSERT OR UPDATE ON TEMP
FOR EACH ROW
WHEN (OLD.N = 0 AND NEW.N=1 OR NEW.N=1)
 BEGIN
    dbms_output.put_line('executing temp_biur'),
END;

The aforementioned trigger fires on INSERTs when N is 1 and on UPDATEs when the value of N changes from 0 to 1.

The WHEN clause can include any Boolean expression. It may include PL/SQL function calls. The following illustrates the use of the SQL function BETWEEN. This trigger fires only when the inserted value is between 1 and 10.

CREATE OR REPLACE TRIGGER temp_aur
AFTER UPDATE ON TEMP
FOR EACH ROW
WHEN (NEW.N BETWEEN 1 AND 10)
BEGIN
    dbms_output.put_line('executing temp_aur'),
END;

The WHEN clause is for ROW triggers only.