Table of Contents Previous Next


2 The SQL Language : 2.3 SQL Commands : 2.3.35 CREATE TRIGGER

CREATE TRIGGER -- define a simple trigger
ON table
[ REFERENCING { OLD AS old | NEW AS new } ...]
[ WHEN condition ]
declaration; [, ...] ]
statement; [, ...]
{ WHEN exception [ OR exception ] [...] THEN
statement; [, ...] } [, ...]
CREATE TRIGGER -- define a compound trigger
ON table
[ REFERENCING { OLD AS old | NEW AS new } ...]
[ WHEN condition ]
COMPOUND TRIGGER
[ private_declaration; ] ...
[ procedure_or_function_definition ] ...
compound_trigger_definition
Where private_declaration is an identifier of a private variable that can be accessed by any procedure or function. There can be zero, one, or more private variables. private_declaration can be any of the following:
REF CURSOR and Cursor Variable Declaration
TYPE Definitions for Records, Collections, and REF CURSORs
Where procedure_or_function_definition :=
Where procedure_definition :=
PROCEDURE proc_name[ argument_list ]
[ options_list ]
{ IS | AS }
END [ proc_name ] ;
Where procedure_body :=
[ declaration; ] [, ...]
statement; [...]
{ WHEN exception [OR exception] [...]] THEN statement; }
Where function_definition :=
FUNCTION func_name [ argument_list ]
RETURN rettype [ DETERMINISTIC ]
[ options_list ]
{ IS | AS }
END [ func_name ] ;
Where function_body :=
[ declaration; ] [, ...]
statement; [...]
{ WHEN exception [ OR exception ] [...] THEN statement; }
Where compound_trigger_definition is:
{ compound_trigger_event } { IS | AS }
compound_trigger_body
END [ compound_trigger_event ] [ ... ]
Where compound_trigger_event :=
Where compound_trigger_body :=
[ declaration; ] [, ...]
statement; [...]
{ WHEN exception [OR exception] [...] THEN statement; }
CREATE TRIGGER defines a new trigger. CREATE OR REPLACE TRIGGER will either create a new trigger, or replace an existing definition.
If you are using the CREATE TRIGGER keywords to create a new trigger, the name of the new trigger must not match any existing trigger defined on the same table. New triggers will be created in the same schema as the table on which the triggering event is defined.
INSTEAD OF trigger modifies an updatable view; the trigger will execute to update the underlying table(s) appropriately. The INSTEAD OF trigger is executed for each row of the view that is updated or modified.
condition is a Boolean expression that determines if the trigger will actually be executed; if condition evaluates to TRUE, the trigger will fire.
If the trigger definition includes the FOR EACH ROW keywords, the WHEN clause can refer to columns of the old and/or new row values by writing OLD.column_name or NEW.column_name respectively. INSERT triggers cannot refer to OLD and DELETE triggers cannot refer to NEW.
If the trigger includes the INSTEAD OF keywords, it may not include a WHEN clause. A WHEN clause cannot contain subqueries.
REFERENCING { OLD AS old | NEW AS new } ...
REFERENCING clause to reference old rows and new rows, but restricted in that old may only be replaced by an identifier named old or any equivalent that is saved in all lowercase (for example, REFERENCING OLD AS old, REFERENCING OLD AS OLD, or REFERENCING OLD AS "old"). Also, new may only be replaced by an identifier named new or any equivalent that is saved in all lowercase (for example, REFERENCING NEW AS new, REFERENCING NEW AS NEW, or REFERENCING NEW AS "new").
Either one, or both phrases OLD AS old and NEW AS new may be specified in the REFERENCING clause (for example, REFERENCING NEW AS New OLD AS Old).
Determines whether the trigger should be fired once for every row affected by the triggering event, or just once per SQL statement. If specified, the trigger is fired once for every affected row (row-level trigger), otherwise the trigger is a statement-level trigger.
PRAGMA AUTONOMOUS_TRANSACTION is the directive that sets the trigger as an autonomous transaction.
A variable, type, REF CURSOR, or subprogram declaration. If subprogram declarations are included, they must be declared after all other variable, type, and REF CURSOR declarations.
An SPL program statement. Note that a DECLARE - BEGIN - END block is considered an SPL statement unto itself. Thus, the trigger body may contain nested blocks.
An exception condition name such as NO_DATA_FOUND, OTHERS, etc.
Then, create a compound trigger named HR_TRIGGER. The trigger utilizes each of the four timing-points to modify the salary with an INSERT, UPDATE, or DELETE statement. In the global declaration section, the initial salary is declared as 10,000:
INSERT the record into table EMP.
The INSERT statement produces the following output:
The UPDATE statement will update the employee salary record, setting the salary to 15000 for a specific employee number.
The UPDATE statement produces the following output:
The DELETE statement deletes the employee salary record.
The DELETE statement produces the following output:
The TRUNCATE statement removes all the records from the EMP table.
The TRUNCATE statement produces the following output:
Note: The TRUNCATE statement may be used only at a BEFORE STATEMENT or AFTER STATEMENT timing-point.

2 The SQL Language : 2.3 SQL Commands : 2.3.35 CREATE TRIGGER

Table of Contents Previous Next