Chapter Seven. Statement Level Triggers
This chapter begins with the same trigger that started Chapter 6, except this is a statement level trigger. We start with a TEMP table.
DROP TABLE TEMP; CREATE TABLE temp (N NUMBER);
Now create an ALTER-INSERT-STATEMENT trigger.
CREATE OR REPLACE TRIGGER temp_ais AFTER INSERT ON TEMP BEGIN dbms_output.put_line('executing temp_ais'); END;
SQL> set feedback off SQL> INSERT INTO temp VALUES (1); -- insert 1 row executing temp_ais SQL> INSERT INTO temp VALUES (1); -- insert 1 row executing temp_ais SQL> INSERT INTO temp SELECT * FROM temp; -- insert 2 rows executing temp_ais
The INSERT statement fires once per SQL statement. For the last insert a row trigger would have to fire twice.
7.1 Sequence of Events
You can do the following with an INSERT STATEMENT trigger:
Figure 7-1 illustrates the behavior of statement level triggers that can perform "Take Action" functions of "Reject the Transaction" functions. The statement level trigger has no knowledge of the before and after values of any changed row. For a comparison with row triggers, compare Figure 7-1 with Figure 6-1.
Figure 7-1. Statement Level Trigger.
Figure 7-1 illustrates the statement level behavior. It also shows that row level triggers fire between the before-statement and after-statement triggers. If three rows are affected by a SQL UPDATE, as is the example in this figure, three row triggers will fire. Statement level triggers fire once.
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
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:
The syntax for statement level triggers is simpler than row triggers. The following features do not exist with statement level triggers:
The combination of row and statement triggers is 12 types of triggers. The following summarizes the template for each trigger.
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;