|Oracle® Database PL/SQL Language Reference
11g Release 1 (11.1)
|PDF · Mobi · ePub|
A stored PL/SQL block associated with a table, a schema, or the database
An anonymous PL/SQL block or a call to a procedure implemented in PL/SQL or Java
the database automatically executes a trigger when specified conditions occur.
BEFORE statement triggers
BEFORE row triggers
AFTER row triggers
AFTER statement triggers
If it is practical, replace the set of individual triggers with different timing points with a single compound trigger that explicitly codes the actions in the order you intend.
If two or more triggers are defined with the same timing point, and the order in which they fire is important, then you can control the firing order using the
FOLLOWS clause (see FOLLOWS).
If multiple compound triggers are specified on a table, then all
BEFORE statement sections will be executed at the
BEFORE statement timing point,
BEFORE row sections will be executed at the
BEFORE row timing point, and so forth. If trigger execution order has been specified using the
FOLLOWS clause, then order of execution of compound trigger sections will be determined by the
FOLLOWS clause. If
FOLLOWS is specified only for some triggers but not all triggers, then the order of execution of triggers is guaranteed only for those that are related using the
To create a trigger in your own schema on a table in your own schema or on your own schema (
SCHEMA), you must have the
TRIGGER system privilege.
To create a trigger in any schema on a table in any schema, or on another user's schema (
SCHEMA), you must have the
TRIGGER system privilege.
In addition to the preceding privileges, to create a trigger on
DATABASE, you must have the
TRIGGER system privilege.
If the trigger issues SQL statements or calls procedures or functions, then the owner of the trigger must have the privileges necessary to perform these operations. These privileges must be granted directly to the owner rather than acquired through roles.
REPLACE to re-create the trigger if it already exists. Use this clause to change the definition of an existing trigger without first dropping it.
Specify the schema to contain the trigger. If you omit
schema, then the database creates the trigger in your own schema.
Specify the name of the trigger to be created.
If a trigger produces compilation errors, then it is still created, but it fails on execution. This means it effectively blocks all triggering DML statements until it is disabled, replaced by a version without compilation errors, or dropped. You can see the associated compiler error messages with the SQL*Plus command
Note:If you create a trigger on a base table of a materialized view, then you must ensure that the trigger does not fire during a refresh of the materialized view. During refresh, the
Use this clause to define a single trigger on a DML event.
BEFORE to cause the database to fire the trigger before executing the triggering event. For row triggers, the trigger is fired before each affected row is changed.
You cannot specify a
BEFORE trigger on a view.
BEFORE statement trigger, or in
BEFORE statement section of a compound trigger, you cannot specify either
BEFORE row trigger or a
BEFORE row section of a compound trigger can read and write into the
AFTER to cause the database to fire the trigger after executing the triggering event. For row triggers, the trigger is fired after each affected row is changed.
You cannot specify an
AFTER trigger on a view.
AFTER statement trigger or in
AFTER statement section of a compound trigger, you cannot specify either
AFTER row trigger or
AFTER row section of a compound trigger can only read but not write into the
Note:When you create a materialized view log for a table, the database implicitly creates an
ROWtrigger on the table. This trigger inserts a row into the materialized view log whenever an
DELETEstatement modifies data in the master table. You cannot control the order in which multiple row triggers fire. Therefore, you should not write triggers intended to affect the content of the materialized view.
See Also:Oracle Database SQL Language Reference for more information about materialized view logs
OF to cause the database to fire the trigger instead of executing the triggering event. You can achieve the same effect when you specify an
ROW section in a compound trigger.
Note:the database fine-grained access control lets you define row-level security policies on views. These policies enforce specified rules in response to DML operations. If an
OFtrigger is also defined on the view, then the database will not enforce the row-level security policies, because the database fires the
OFtrigger instead of executing the DML on the view.
OF triggers are valid for DML events on any views. They are not valid for DDL or database events, and you cannot specify an
OF trigger on a table.
You can read both the :
OLD and the :
NEW value, but you cannot write either the :
OLD or the :
If a view is inherently updatable and has
OF triggers, then the triggers take preference. The database fires the triggers instead of performing DML on the view.
If the view belongs to a hierarchy, then the trigger is not inherited by subviews.
See Also:Creating an INSTEAD OF Trigger: Example
DML_event_clause lets you specify one of three DML statements that can cause the trigger to fire. the database fires the trigger in the existing user transaction.
You cannot specify the
MERGE keyword in the
DML_event_clause. If you want a trigger to fire in relation to a
MERGE operation, then you must create triggers on the
UPDATE operations to which the
MERGE operation decomposes.
See Also:Creating a DML Trigger: Examples
UPDATE if you want the database to fire the trigger whenever an
UPDATE statement changes a value in one of the columns specified after
OF. If you omit
OF, then the database fires the trigger whenever an
UPDATE statement changes a value in any column of the table or nested table.
UPDATE trigger, you can specify object type, varray, and
REF columns after
OF to indicate that the trigger should be fired whenever an
UPDATE statement changes a value in one of the columns. However, you cannot change the values of these columns in the body of the trigger itself.
Note:Using OCI functions or the
DBMS_LOBpackage to update LOB values or LOB attributes of object columns does not cause the database to fire triggers defined on the table containing the columns or the attributes.
You cannot specify
OF for an
OF trigger. the database fires
OF triggers whenever an
UPDATE changes a value in any column of the view.
You cannot specify a nested table or LOB column in the
VIEWin Oracle Database SQL Language Reference for a list of constructs that prevent inserts, updates, or deletes on a view
Performing DML operations directly on nested table columns does not cause the database to fire triggers defined on the table containing the nested table column.
ON table | view The
ON clause lets you determine the database object on which the trigger is to be created. Specify the
view name of one of the following on which the trigger is to be created:
Table or view
Object table or object view
A column of nested-table type
If you omit
schema, then the database assumes the table is in your own schema.
referencing_clause lets you specify correlation names. You can use correlation names in the trigger body and
WHEN condition of a row trigger to refer specifically to old and new values of the current row. The default correlation names are
NEW. If your row trigger is associated with a table named
NEW, then use this clause to specify different correlation names to avoid confusion between the table name and the correlation name.
If the trigger is defined on a nested table, then
NEW refer to the row of the nested table, and
PARENT refers to the current row of the parent table.
If the trigger is defined on an object table or view, then
NEW refer to object instances.
ROW to designate the trigger as a row trigger. the database fires a row trigger once for each row that is affected by the triggering statement and meets the optional trigger constraint defined in the
OF triggers, if you omit this clause, then the trigger is a statement trigger. the database fires a statement trigger only once when the triggering statement is issued if the optional trigger constraint is met.
OF trigger statements are implicitly activated for each row.
Use this clause to define a compound trigger on a DML event. The body of a
COMPOUND trigger can have up to four sections, so that you can specify a before statement, before row, after row, or after statement operation in one trigger.
See Also:Compound Trigger Restrictions for additional restrictions
Use this clause to define a single trigger on a DDL or database event.
Specify one or more types of DDL statements that can cause the trigger to fire. You can create triggers for these events on
SCHEMA unless otherwise noted. You can create
AFTER triggers for these events. the database fires the trigger in the existing user transaction.
See Also:Creating a DDL Trigger: Example
ddl_event values are valid:
See Also:Oracle Database SQL Language Reference for information about using the SQL statement
ANALYZEto collect statistics
CREATE to fire the trigger whenever a
CREATE statement adds a new database object to the data dictionary. The trigger will not be fired by a
Specify one or more particular states of the database that can cause the trigger to fire. You can create triggers for these events on
SCHEMA unless otherwise noted. For each of these triggering events, the database opens an autonomous transaction scope, fires the trigger, and commits any separate transaction (regardless of any existing user transaction).
Each database event is valid in either a
BEFORE trigger or an
AFTER trigger, but not both. The following
database_event values are valid:
AFTER DB_ROLE_CHANGE In a Data Guard configuration, specify
DB_ROLE_CHANGE to fire the trigger whenever a role change occurs from standby to primary or from primary to standby. This event is valid only with
DATABASE, not with
The following errors do not cause a
SERVERERROR trigger to fire:
ORA-01403: no data found
ORA-01422: exact fetch returns more than requested number of rows
ORA-01423: error encountered while checking for extra rows in exact fetch
ORA-01034: ORACLE not available
ORA-04030: out of process memory when trying to allocate string bytes (string, string)
See Also:Doing Independent Units of Work with Autonomous Transactions for information about autonomous transactions
See Also:Creating a SCHEMA Trigger: Example
This clause lets you specify the relative firing order of triggers of the same type. Use
FOLLOWS to indicate that the trigger being created should fire after the specified triggers.
The specified triggers must already exist, they must be defined on the same table as the trigger being created, and they must have been successfully compiled. They need not be enabled.
You can specify
FOLLOWS in the definition of a simple trigger with a compound trigger target, or in the definition of a compound trigger with a simple trigger target. In these cases, the
FOLLOWS keyword applies only to the section of the compound trigger with the same timing point as the sample trigger. If the compound trigger has no such timing point, then
FOLLOWS is quietly ignored.
See Also:Order of Trigger Firing for more information about the order in which the database fires triggers
Use this clause to create the trigger in an enabled or disabled state. Creating a trigger in a disabled state lets you ensure that the trigger compiles without errors before you put into actual use.
DISABLE to create the trigger in disabled form. You can subsequently issue an
TRIGGERS statement to enable the trigger. If you omit this clause, then the trigger is enabled when it is created.
Specify the trigger condition, which is a SQL condition that must be satisfied for the database to fire the trigger. This condition must contain correlation names and cannot contain a query.
OLD keywords, when specified in the
WHEN clause, are not considered bind variables, so are not preceded by a colon (:). However, you must precede
OLD with a colon in all references other than the
If you specify this clause for a DML event trigger, then you must also specify
ROW. the database evaluates this condition for each row affected by the triggering statement.
You cannot specify trigger conditions for
OF trigger statements.
You can reference object columns or their attributes, or varray, nested table, or LOB columns. You cannot invoke PL/SQL functions or methods in the trigger condition.
Specify the PL/SQL block, PL/SQL compound trigger block, or call procedure that the database executes to fire the trigger.
Timing point sections can be in any order, but no timing point section can be repeated. The
declare_section of a compound trigger block cannot include
Creating a DML Trigger: Examples This example shows the basic syntax for a
BEFORE statement trigger. You would write such a trigger to place restrictions on DML statements issued on a table, for example, when such statements could be issued.
CREATE TRIGGER schema.trigger_name BEFORE DELETE OR INSERT OR UPDATE ON schema.table_name pl/sql_block
the database fires such a trigger whenever a DML statement affects the table. This trigger is a
BEFORE statement trigger, so the database fires it once before executing the triggering statement.
The next example shows a partial
BEFORE row trigger. The PL/SQL block might specify, for example, that an employee's salary must fall within the established salary range for the employee's job:
CREATE TRIGGER hr.salary_check BEFORE INSERT OR UPDATE OF salary, job_id ON hr.employees FOR EACH ROW WHEN (new.job_id <> 'AD_VP') pl/sql_block
the database fires this trigger whenever one of the following statements is issued:
INSERT statement that adds rows to the
UPDATE statement that changes values of the
job_id columns of the
salary_check is a
BEFORE row trigger, so the database fires it before changing each row that is updated by the
UPDATE statement or before adding each row that is inserted by the
salary_check has a trigger condition that prevents it from checking the salary of the administrative vice president (
Creating a DDL Trigger: Example This example creates an
AFTER statement trigger on any DDL statement
CREATE. Such a trigger can be used to audit the creation of new data dictionary objects in your schema.
CREATE TRIGGER audit_db_object AFTER CREATE ON SCHEMA pl/sql_block
Calling a Procedure in a Trigger Body: Example You could create the
salary_check trigger described in the preceding example by calling a procedure instead of providing the trigger body in a PL/SQL block. Assume you have defined a procedure
check_sal in the
hr schema, which verifies that an employee's salary is in an appropriate range. Then you could create the trigger
salary_check as follows:
CREATE TRIGGER salary_check BEFORE INSERT OR UPDATE OF salary, job_id ON employees FOR EACH ROW WHEN (new.job_id <> 'AD_VP') CALL check_sal(:new.job_id, :new.salary, :new.last_name)
check_sal could be implemented in PL/SQL, C, or Java. Also, you can specify :
OLD values in the
CALL clause instead of :
Creating a Database Event Trigger: Example This example shows the basic syntax for a trigger to log all errors. The hypothetical PL/SQL block does some special processing for a particular error (invalid logon, error number 1017). This trigger is an
AFTER statement trigger, so it is fired after an unsuccessful statement execution, such as unsuccessful logon.
CREATE TRIGGER log_errors AFTER SERVERERROR ON DATABASE BEGIN IF (IS_SERVERERROR (1017)) THEN <special processing of logon error> ELSE <log error number> END IF; END;
CREATE VIEW order_info AS SELECT c.customer_id, c.cust_last_name, c.cust_first_name, o.order_id, o.order_date, o.order_status FROM customers c, orders o WHERE c.customer_id = o.customer_id;
Normally this view would not be updatable, because the primary key of the
orders table (
order_id) is not unique in the result set of the join view. To make this view updatable, create an
OF trigger on the view to process
INSERT statements directed to the view.
CREATE OR REPLACE TRIGGER order_info_insert INSTEAD OF INSERT ON order_info DECLARE duplicate_info EXCEPTION; PRAGMA EXCEPTION_INIT (duplicate_info, -00001); BEGIN INSERT INTO customers (customer_id, cust_last_name, cust_first_name) VALUES ( :new.customer_id, :new.cust_last_name, :new.cust_first_name); INSERT INTO orders (order_id, order_date, customer_id) VALUES ( :new.order_id, :new.order_date, :new.customer_id); EXCEPTION WHEN duplicate_info THEN RAISE_APPLICATION_ERROR ( num=> -20107, msg=> 'Duplicate customer or order ID'); END order_info_insert; /
You can now insert into both base tables through the view (as long as all
NULL columns receive values):
INSERT INTO order_info VALUES (999, 'Smith', 'John', 2500, '13-MAR-2001', 0);
For more information about
OF triggers, see Modifying Complex Views (INSTEAD OF Triggers).
Creating a SCHEMA Trigger: Example The following example creates a
BEFORE statement trigger on the sample schema
hr. When a user connected as
hr attempts to drop a database object, the database fires the trigger before dropping the object:
CREATE OR REPLACE TRIGGER drop_trigger BEFORE DROP ON hr.SCHEMA BEGIN RAISE_APPLICATION_ERROR ( num => -20000, msg => 'Cannot drop object'); END; /