Chapter Seven. Statement Level Triggers

7.1 SEQUENCE OF EVENTS

7.2 INSERT STATEMENT TRIGGER SYNTAX

7.3 STATEMENT LEVEL AGGREGATION

7.4 PROCESSING ROW CAPTURED DATA

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;

The following inserts have different results than the beginning of Chapter 6, which demonstrated a ROW trigger. We begin by inserting a single row.

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:

You can perform an aggregate computation on the table. This can be computed before or after the insert.
You can use a statement level trigger to process data that is collected by a row trigger. This is covered in Section 7.4, "Processing Row Captured Data."
You can signal an event. This can simply be a print statement. It can also be an email or signal to another process using the DBMS_ALERT package.

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.

graphics/07fig01.gif

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 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;

Flylib.com

Category list

Similar pages

Chapter Seven. Statement Level Triggers