|Oracle9i SQL Reference
Release 2 (9.2)
Part Number A96540-01
SQL Statements: CREATE SYNONYM to CREATE TRIGGER, 6 of 6
TRIGGER statement to create and enable a database trigger, which is
Oracle automatically executes a trigger when specified conditions occur.
When you create a trigger, Oracle enables it automatically. You can subsequently disable and enable a trigger with the
ENABLE clause of the
Before a trigger can be created, the user
SYS must run a SQL script commonly called
DBMSSTDX.SQL. The exact name and location of this script depend on your operating system.
SCHEMA), you must have the
SCHEMA), you must have the
DATABASE, you must have the
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 Oracle 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
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
BEFORE to cause Oracle to fire the trigger before executing the triggering event. For row triggers, the trigger is fired before each affected row is changed.
Restrictions on BEFORE triggers:
BEFOREtrigger on a view or an object view.
NEWvalue but not to the :
AFTER to cause Oracle to fire the trigger after executing the triggering event. For row triggers, the trigger is fired after each affected row is changed.
Restrictions on AFTER triggers:
AFTERtrigger on a view or an object view.
OLDor the :
When you create a materialized view log for a table, Oracle implicitly creates an
CREATE MATERIALIZED VIEW LOG for more information on materialized view logs
OF to cause Oracle to fire the trigger instead of executing the triggering event.
OF triggers are valid for DML events on views. They are not valid for DDL or database events.
If a view is inherently updatable and has
OF triggers, then the triggers take preference. In other words, Oracle 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.
Restrictions on INSTEAD OF triggers:
OFtriggers are valid only for views. You cannot specify an
OFtrigger on a table.
OLDand the :
NEWvalue, but you cannot write either the :
OLDor the :
You can create multiple triggers of the same type (
dml_event_clause lets you specify one of three DML statements that can cause the trigger to fire. Oracle fires the trigger in the existing user transaction.
DELETE if you want Oracle to fire the trigger whenever a
DELETE statement removes a row from the table or removes an element from a nested table.
INSERT if you want Oracle to fire the trigger whenever an
INSERT statement adds a row to table or adds an element to a nested table.
UPDATE if you want Oracle to fire the trigger whenever an
UPDATE statement changes a value in one of the columns specified after
OF. If you omit
OF, then Oracle 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.
Using OCI functions or the
Restrictions on triggers on UPDATE operations:
OFtrigger. Oracle fires
OFtriggers whenever an
UPDATEchanges a value in any column of the view.
Performing DML operations directly on nested table columns does not cause Oracle to fire triggers defined on the table containing the nested table column.
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. Oracle fires the trigger in the existing user transaction.
Restriction on triggers on DDL events: You cannot specify as a triggering event any DDL operation performed through a PL/SQL procedure.
ddl_event values are valid:
ALTER to fire the trigger whenever an
ALTER statement modifies a database object in the data dictionary.
Restriction on triggers on ALTER operations: The trigger will not be fired by an
ANALYZE to fire the trigger whenever Oracle collects or deletes statistics or validates the structure of a database object.
STATISTICS to fire the trigger whenever Oracle associates a statistics type with a database object.
AUDIT to fire the trigger whenever Oracle tracks the occurrence of a SQL statement or tracks operations on a schema object.
COMMENT to fire the trigger whenever a comment on a database object is added to the data dictionary.
CREATE to fire the trigger whenever a
CREATE statement adds a new database object to the data dictionary.
Restriction on triggers on CREATE operations: The trigger will not be fired by a
STATISTICS to fire the trigger whenever Oracle disassociates a statistics type from a database object.
DROP to fire the trigger whenever a
DROP statement removes a database object from the data dictionary.
GRANT to fire the trigger whenever a user grants system privileges or roles or object privileges to another user or to a role.
NOAUDIT to fire the trigger whenever a
NOAUDIT statement instructs Oracle to stop tracking a SQL statement or operations on a schema object.
RENAME to fire the trigger whenever a
RENAME statement changes the name of a database object.
REVOKE to fire the trigger whenever a
REVOKE statement removes system privileges or roles or object privileges from a user or role.
TRUNCATE to fire the trigger whenever a
TRUNCATE statement removes the rows from a table or cluster and resets its storage characteristics.
DDL to fire the trigger whenever any of the preceding DDL statements is issued.
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, Oracle opens an autonomous transaction scope, fires the trigger, and commits any separate transaction (regardless of any existing user transaction).
SERVERERROR to fire the trigger whenever a server error message is logged.
The following errors do not cause a
SERVERERROR trigger to fire:
ORA-01403: data not 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
LOGON to fire the trigger whenever a client application logs onto the database.
LOGOFF to fire the trigger whenever a client applications logs off the database.
STARTUP to fire the trigger whenever the database is opened.
SHUTDOWN to fire the trigger whenever an instance of the database is shut down.
SUSPEND to fire the trigger whenever a server error causes a transaction to be suspended.
PL/SQL User's Guide and Reference for more information on autonomous transaction scope
ON clause lets you determine the database object on which the trigger is to be created.
Specify the schema and table or view name of one of the following on which the trigger is to be created:
If you omit schema, then Oracle assumes the table is in your own schema. You can create triggers on index-organized tables.
Restriction on schema: You cannot create a trigger on a table in the schema
Specify the nested_table_column of a view upon which the trigger is being defined. Such a trigger will fire only if the DML operates on the elements of the nested table.
Restriction on triggers on nested tables: You can specify
TABLE only for
DATABASE to define the trigger on the entire database. The trigger fires whenever any database user initiates the triggering event.
SCHEMA to define the trigger on the current schema. The trigger fires whenever any user connected as
schema initiates the triggering event.
The referencing_clause lets you specify correlation names. You can use correlation names in the PL/SQL block 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, use this clause to specify different correlation names to avoid confusion between the table name and the correlation name.
NEWrefer to the row of the nested table, and
PARENTrefers to the current row of the parent table.
NEWrefer to object instances.
Restriction on the referencing_clause: The
referencing_clause is not valid with
OF triggers on
CREATE DDL events.
ROW to designate the trigger as a row trigger. Oracle 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. Oracle 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.
Restriction on row triggers: This clause is valid only for DML event triggers (not DDL or database event triggers).
Specify the trigger restriction, which is a SQL condition that must be satisfied for Oracle to fire the trigger. See the syntax description of condition in Chapter 5, "Conditions". 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
Restrictions on trigger conditions:
ROW. Oracle evaluates this condition for each row affected by the triggering statement.
Specify the PL/SQL block that Oracle executes to fire the trigger.
The PL/SQL block of a database trigger can contain one of a series of built-in functions in the
SYS schema designed solely to extract system event attributes. These functions can be used only in the PL/SQL block of a database trigger.
Restrictions on trigger implementation:
CONSTRAINT) if the block is executed within the same transaction.
NEWvalues but not the :
OLDvalues of LOB columns within the trigger action.
The call_procedure_statement lets you call a stored procedure, rather than specifying the trigger code inline as a PL/SQL block. The syntax of this statement is the same as that for
CALL, with the following exceptions:
CALL, because it applies only to functions.
This example creates a
BEFORE statement trigger named
emp_permit_changes in the schema
hr. You would write such a trigger to place restrictions on DML statements issued on this table (such as when such statements could be issued).
CREATE TRIGGER hr.emp_permit_changes BEFORE DELETE OR INSERT OR UPDATE ON hr.employees < pl/sql block >
Oracle fires this trigger whenever a
UPDATE statement affects the
employees table in the schema
hr. The trigger
emp_permit_changes is a
BEFORE statement trigger, so Oracle fires it once before executing the triggering statement.
This example creates a
BEFORE row trigger named
salary_check in the schema
hr. The PL/SQL block might specify, for example, that the 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 >
Oracle fires this trigger whenever one of the following statements is issued:
INSERTstatement that adds rows to the
UPDATEstatement that changes values of the
job_idcolumns of the
salary_check is a
BEFORE row trigger, so Oracle 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 restriction that prevents it from checking the salary of the administrative vice president (
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.
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
hr.salary_check, which verifies that an employee's salary is in an appropriate range. Then you could create the trigger
salary_check as follows:
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') 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 :
This example creates 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;
In this example, an
oe.order_info view is created to display information about customers and their orders:
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 (the PL/SQL trigger implementation is shown in italics):
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):
The following example creates a
BEFORE statement trigger on the sample schema
hr. Whenever a user connected as
hr attempts to drop any database object, Oracle fires the trigger before dropping the object: