CREATE TRIGGER v12
Name
CREATE TRIGGER
-- define a simple trigger.
Synopsis
Name
CREATE TRIGGER
-- define a compound trigger.
Synopsis
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:
- Variable Declaration
- Record Declaration
- Collection Declaration
REF CURSOR
and Cursor Variable DeclarationTYPE
Definitions for Records, Collections, andREF CURSORs
- Exception
- Object Variable Declaration
Where procedure_or_function_definition
:=
Where procedure_definition
:=
Where procedure_body
:=
Where function_definition
:=
Where function_body
:=
Where compound_trigger_definition
:=
Where compound_trigger_event
:=
Where compound_trigger_body
:=
Description
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.
If you are updating the definition of an existing trigger, use the CREATE OR REPLACE TRIGGER
keywords.
When you use syntax that is compatible with Oracle to create a trigger, the trigger runs as a SECURITY DEFINER
function.
Parameters
name
The name of the trigger to create.
BEFORE | AFTER
Determines whether the trigger is fired before or after the triggering event.
INSTEAD OF
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.
INSERT | UPDATE | DELETE | TRUNCATE
Defines the triggering event.
table
The name of the table or view on which the triggering event occurs.
condition
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 simple trigger definition includes the
FOR EACH ROW
keywords, theWHEN
clause can refer to columns of the old and/or new row values by writingOLD.column_name
orNEW.column_name
respectively.INSERT
triggers cannot refer toOLD
andDELETE
triggers cannot refer toNEW
.If the trigger includes the
INSTEAD OF
keywords, it may not include aWHEN
clause. AWHEN
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
).
This clause is not compatible with Oracle databases in that identifiers other than old
or new
may not be used.
FOR EACH ROW
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
PRAGMA AUTONOMOUS_TRANSACTION
is the directive that sets the trigger as an autonomous transaction.
declaration
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.
statement
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.
exception
An exception condition name such as NO_DATA_FOUND
, OTHERS
, etc.
Examples
The following is a statement-level trigger that fires after the triggering statement (insert, update, or delete on table emp
) is executed.
The following is a row-level trigger that fires before each row is either inserted, updated, or deleted on table emp
.
The following is an example of a compound trigger that records a change to the employee salary by defining a compound trigger hr_trigger
on table emp
.
First, create a table named emp
.
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.
See Also