6.3 Insert Row Trigger Syntax

The Insert Row trigger has the following syntax.

 CREATE OR REPLACE TRIGGER  trigger_name  AFTERBEFORE 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_[AB] [IUD] [RS] 

trigger_name	Trigger names are limited to 30 characters . You have to abbreviate the table name to append trigger attribute information. Long table names should have a consistent abbreviation. The table name is used in the naming of foreign key constraints and trigger names. A consistent abbreviation shortens the troubleshooting process.
[AB]	Indicates the BEFORE or AFTER part of the trigger type.
[IUD]	Indicates the triggering event. The triggering event can be "INSERT." It can be "UPDATE." It can be "INSERT OR UPDATE OR DELETE."
[RS]	Indicates the ROW or STATEMENT part of the trigger type.

Examples of trigger names:

Table	Trigger Type	Triggering Event	Trigger Name
STUDENTS	BEFORE EACH ROW	INSERT	students_bir
STUDENTS	AFTER EACH ROW	INSERT OR UPDATE	students_aiur
STUDENTS	AFTER STATEMENT	INSERT	students_ais

AFTERBEFORE 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:

NEW.COLUMN_NAME	This is the syntax for referencing a column in the WHEN clause of an INSERT or UPDATE trigger.
OLD.COLUMN_NAME	This can be used in the WHEN clause of UPDATE and DELETE ROW triggers. This does not evaluate in INSERT ROW triggers.

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:

OLD.N=0 AND NEW.N=1	This expression does not evaluate on any INSERT statements because OLD.N is NULL. It may be TRUE or FALSE on UPDATE statements.
OR
NEW.N=1	This evaluates on INSERT and UPDATE statements.

 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.