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:
| ALL ROW triggers only. |
| 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;