7.2 Insert Statement Trigger Syntax

The Insert Statement Trigger has the following syntax.

 CREATE OR REPLACE TRIGGER trigger_name [AFTER  BEFORE] INSERT ON table_name DECLARE  Local declarations  BEGIN  Body written PL/SQL  END; 

The key difference in the syntax between the statement and row trigger is the FOR EACH ROW clause ”this clause specifically identifies the trigger as row level and is not in the statement level trigger.

The statement trigger syntax that designates the triggering event is the same as row triggers. Refer to Chapter 6, "Row Trigger Syntax," for a thorough discussion on trigger naming conventions and the OF COLUMN_NAME clause. The following are valid clauses for statement level triggers, as well as row triggers.

 BEFORE INSERT OR UPDATE OR DELETE ON  table_name  AFTER INSERT OR UPDATE OF  column_name  OR DELETE ON  table_name  

The following are two key points with regard to trigger options:

Table .

WHEN (Boolean expression)	ALL ROW triggers only.
OF column_name clause	Valid for UPDATE ROW and STATEMENT triggers only.

The syntax for statement level triggers is simpler than row triggers. The following features do not exist with statement level triggers:

• There is no WHEN (Boolean expression) that voids any discussion of OLD.COLUMN_NAME and NEW.COLUMN_NAME expressions.

• References to :NEW.COLUMN_NAME and :OLD.COLUMN_NAME are invalid.

The combination of row and statement triggers is 12 types of triggers. The following summarizes the template for each trigger.

Trigger Type	Triggering Event	Create or Replace (example: TEMP table)
BEFORE STATEMENT	INSERT	TRIGGER TEMP BEFORE INSERT ON TEMP BEGIN Body END
BEFORE EACH ROW [a]	INSERT	TRIGGER TEMP BEFORE INSERT ON TEMP FOR EACH ROW BEGIN Body END
AFTER EACH ROW [a]	INSERT	TRIGGER TEMP AFTER INSERT ON TEMP FOR EACH ROW BEGIN Body END
AFTER STATEMENT	INSERT	TRIGGER TEMP AFTER INSERT ON TEMP BEGIN Body END
BEFORE STATEMENT	UPDATE [b]	TRIGGER TEMP BEFORE UPDATE ON TEMP BEGIN Body END
BEFORE EACH ROW [a]	UPDATE [b]	TRIGGER TEMP BEFORE UPDATE ON TEMP FOR EACH ROW BEGIN Body END
AFTER EACH ROW [a]	UPDATE [b]	TRIGGER TEMP AFTER UPDATE ON TEMP FOR EACH ROW BEGIN Body END
AFTER STATEMENT	UPDATE [b]	TRIGGER TEMP AFTER UPDATE ON TEMP BEGIN Body END
BEFORE STATEMENT	DELETE	TRIGGER TEMP BEFORE DELETE ON TEMP BEGIN Body END
BEFORE EACH ROW [a]	DELETE	TRIGGER TEMP BEFORE DELETE ON TEMP FOR EACH ROW BEGIN Body END
AFTER EACH ROW [a]	DELETE	TRIGGER TEMP AFTER DELETE ON TEMP FOR EACH ROW BEGIN Body END
AFTER STATEMENT	DELETE	TRIGGER TEMP AFTER DELETE ON TEMP BEGIN Body END

^[a] WHEN (Boolean expression) is optional with all row triggers.

^[b] OF COLUMN_NAME clause is optional on UPDATE ROW and STATEMENT triggers.

Although this table illustrates 12 distinct triggers, any trigger type, such as a BEFORE STATEMENT trigger, can combine triggering events such as the following:

 CREATE OR REPLACE TRIGGER temp_biuds BEFORE INSERT OR UPDATE OR DELETE ON TEMP BEGIN     CASE     WHEN inserting THEN  PL/SQL code here  WHEN updating THEN  PL/SQL code here  WHEN deleting THEN  PL/SQL code here  END CASE; END;