Oracle8i SQL Reference Release 2 (8.1.6) A76989-01 |
|
SQL Statements (continued), 2 of 10
To create and enable a database trigger. A database trigger is
Oracle automatically executes a trigger when specified conditions occur.
See Also:
|
Before a trigger can be created, the user SYS must run the SQL script DBMSSTDX.SQL
. The exact name and location of this script depend on your operating system.
SCHEMA
), you must have the CREATE
TRIGGER
privilege.
SCHEMA
), you must have the CREATE
ANY
TRIGGER
privilege.
DATABASE
, you must have the ADMINISTER
DATABASE
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.
|
creates a new trigger. When you create a trigger, Oracle enables it automatically. You can subsequently disable and enable a trigger with the |
|
|
If a trigger produces compilation errors, it is still created, but it fails on execution. You can see the associated compiler error messages with the SQL*Plus command |
|
|
re-creates the trigger if it already exists. Use this clause to change the definition of an existing trigger without first dropping it. |
|
schema |
is the schema to contain the trigger. If you omit schema, Oracle creates the trigger in your own schema. |
|
trigger |
is the name of the trigger to be created. |
|
|
Note: If you create a trigger on a base table of a materialized view, you must ensure that the trigger does not fire during a refresh of the materialized view. (During refresh, the |
|
|
causes Oracle to fire the trigger before executing the triggering event. For row triggers, this is a separate firing before each affected row is changed. Restrictions: |
|
|
causes Oracle to fire the trigger after executing the triggering event. For row triggers, this is a separate firing after each affected row is changed. Restrictions: |
|
|
Note: When you create a materialized view log for a table, Oracle implicitly creates an See Also: "CREATE MATERIALIZED VIEW LOG / SNAPSHOT LOG" for more information on materialized view logs. |
|
|
causes Oracle to fire the trigger instead of executing the triggering event. By default,
If a view is inherently updatable and has |
|
|
Restrictions:
|
|
Note: You can create multiple triggers of the same type ( |
||
dml_event_clause |
specifies one of three DML statements that can cause the trigger to fire. Oracle fires the trigger in the existing user transaction. |
|
|
|
causes Oracle to fire the trigger whenever a |
|
|
causes Oracle to fire the trigger whenever an |
|
|
causes Oracle to fire the trigger whenever an
For an |
|
|
Note: Using OCI functions or the |
|
|
Restrictions:
|
|
Performing DML operations directly on nested table columns does not cause Oracle to fire triggers defined on the table containing the nested table column |
|
ddl_event |
is one or more types of DDL statements that can cause the trigger to fire. You can create triggers for these events on |
|
|
|
fires the trigger whenever an
Restriction: The trigger will not be fired by an |
|
|
fires the trigger whenever Oracle collects or deletes statistics or validates the structure of a database object. |
|
|
fires the trigger whenever Oracle associates a statistics type with a database object. |
|
|
fires the trigger whenever Oracle tracks the occurrence of a SQL statement or tracks operations on a schema object. |
|
|
fires the trigger whenever a comment on a database object is added to the data dictionary. |
|
|
fires the trigger whenever a
Restriction: The trigger will not be fired by a |
|
|
fires the trigger whenever Oracle disassociates a statistics type from a database object. |
|
|
fires the trigger whenever a |
|
|
fires the trigger whenever a user grants system privileges or roles or object privileges to another user or to a role. |
|
|
fires the trigger whenever a |
|
|
fires the trigger whenever a |
|
|
fires the trigger whenever a |
|
|
fires the trigger whenever a |
|
|
fires the trigger whenever any of the preceding DDL statements is issued. |
|
Restriction: You cannot specify as a triggering event any DDL operation performed through a PL/SQL procedure. |
|
database_event |
describes a particular state of the database that can cause the trigger to fire. You can create triggers for these events on See Also: PL/SQL User's Guide and Reference for more information on autonomous transaction scope. |
|
|
|
causes Oracle to fire the trigger whenever a server error message is logged. |
|
|
Note: The following errors do not cause a |
|
|
causes Oracle to fire the trigger whenever a client application logs onto the database. |
|
|
causes Oracle to fire the trigger whenever a client applications logs off the database. |
|
|
causes Oracle to fire the trigger whenever the database is opened. |
|
|
causes Oracle to fire the trigger whenever an instance of the database is shut down. |
|
Notes: |
|
|
determines the database object on which the trigger is to be created. |
|
|
[schema.] table | view |
specifies the schema and table or view name of one of the following on which the trigger is to be created: |
|
|
If you omit schema, Oracle assumes the table is in your own schema. You can create triggers on index-organized tables. Restriction: You cannot create a trigger on a table in the schema SYS. |
|
|
specifies that the trigger is being defined on column nested_table_column of a view. Such a trigger will fire only if the DML operates on the elements of the nested table.
Restriction: You can specify |
|
|
specifies that the trigger is being defined on the entire database. |
|
|
specifies that the trigger is being defined on the current schema. |
referencing_clause |
specifies correlation names. You can use correlation names in the PL/SQL block and |
|
|
Restriction: This clause is valid only for DML event triggers (not DDL or database event triggers). |
|
|
designates the trigger to be 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 WHEN condition. Note: This clause is applies only to DML events, not to DDL or database events. |
|
|
Except for
|
|
|
specifies 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 "Conditions". This condition must contain correlation names and cannot contain a query. |
|
|
Restrictions:
|
|
pl/sql_block |
is the PL/SQL block that Oracle executes to fire the trigger. For information on PL/SQL, including how to write PL/SQL blocks, see PL/SQL User's Guide and Reference. |
|
|
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. For information on these functions, see Oracle8i Application Developer's Guide - Fundamentals. |
|
|
Restrictions:
See Also: Oracle8i Application Developer's Guide - Fundamentals. |
|
call_procedure_statement |
enables you to call a stored procedure, rather than specifying inline the trigger code as a PL/SQL block. The syntax of this statement is the same as that for "CALL", with the following exceptions: |
|
|
|
This example creates a BEFORE
statement trigger named EMP_PERMIT_CHANGES
in the schema SCOTT
. 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 scott.emp_permit_changes BEFORE DELETE OR INSERT OR UPDATE ON scott.emp pl/sql block
Oracle fires this trigger whenever a DELETE
, INSERT
, or UPDATE
statement affects the EMP
table in the schema SCOTT
. 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 SCOTT
. 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 scott.salary_check BEFORE INSERT OR UPDATE OF sal, job ON scott.emp FOR EACH ROW WHEN (new.job <> 'PRESIDENT') pl/sql_block
Oracle fires this trigger whenever one of the following statements is issued:
INSERT
statement that adds rows to the EMP
table
UPDATE
statement that changes values of the SAL
or JOB
columns of the EMP
table
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 INSERT
statement.
SALARY_CHECK
has a trigger restriction that prevents it from checking the salary of the company president.
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 SCOTT
.CHECK_SAL
, which verifies that an employee's salary in in an appropriate range. Then you could create the trigger SALARY_CHECK
as follows:
CREATE TRIGGER scott.salary_check BEFORE INSERT OR UPDATE OF sal, job ON scott.emp FOR EACH ROW WHEN (new.job<> 'PRESIDENT') CALL check_sal(:new.job, :new.sal, :new.ename)
The procedure CHECK_SAL
could be implemented in PL/SQL, C, or Java. Also, you can specify :OLD
values in the CALL
clause instead of :NEW
values.
This example creates a trigger to log all errors. The 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;
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 TRIOGGER audit_db_object AFTER CREATE ON SCHEMA pl/sql_block
In this example, customer data is stored in two tables. The object view ALL_CUSTOMERS
is created as a UNION
of the two tables, CUSTOMERS_SJ
and CUSTOMERS_PA
. An INSTEAD OF
trigger is used to insert values.
CREATE TABLE customers_sj ( cust NUMBER(6), address VARCHAR2(50), credit NUMBER(9,2) ); CREATE TABLE customers_pa ( cust NUMBER(6), address VARCHAR2(50), credit NUMBER(9,2) ); CREATE TYPE customer_t AS OBJECT ( cust NUMBER(6), address VARCHAR2(50), credit NUMBER(9,2), location VARCHAR2(20) ); CREATE VIEW all_customers (cust) AS SELECT customer_t (cust, address, credit, 'SAN_JOSE') FROM customers_sj UNION ALL SELECT customer_t (cust, address, credit, 'PALO_ALTO') FROM customers_pa; CREATE TRIGGER instrig INSTEAD OF INSERT ON all_customers FOR EACH ROW BEGIN IF (:new.cust.location = 'SAN_JOSE') THEN INSERT INTO customers_sj VALUES (:new.cust.cust, :new.cust.address,:new.cust.credit); ELSE INSERT INTO customers_pa VALUES (:new.cust.cust, :new.cust.address, :new.cust.credit); END IF; END;
|
![]() Copyright © 1999 Oracle Corporation. All Rights Reserved. |
|