7.2 Insert Statement Trigger Syntax

[ Team LiB ]

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;

[ Team LiB ]