Raise application error in oracle trigger

9 PL/SQL Triggers

A trigger is like a stored procedure that Oracle Database invokes automatically whenever a specified event occurs.

The database can detect only system-defined events. You cannot define your own events.

Overview of Triggers

Like a stored procedure, a trigger is a named PL/SQL unit that is stored in the database and can be invoked repeatedly. Unlike a stored procedure, you can enable and disable a trigger, but you cannot explicitly invoke it. While a trigger is enabled , the database automatically invokes it—that is, the trigger fires —whenever its triggering event occurs. While a trigger is disabled , it does not fire.

You create a trigger with the CREATE TRIGGER statement. You specify the triggering event in terms of triggering statements and the item on which they act. The trigger is said to be created on or defined on the item, which is either a table, a view, a schema, or the database. You also specify the timing point , which determines whether the trigger fires before or after the triggering statement runs and whether it fires for each row that the triggering statement affects. By default, a trigger is created in the enabled state. For more information about the CREATE TRIGGER statement, see «CREATE TRIGGER Statement».

If the trigger is created on a table or view, then the triggering event is composed of DML statements, and the trigger is called a DML trigger . For more information, see «DML Triggers».

If the trigger is created on a schema or the database, then the triggering event is composed of either DDL or database operation statements, and the trigger is called a system trigger . For more information, see «System Triggers».

A conditional trigger has a WHEN clause that specifies a SQL condition that the database evaluates for each row that the triggering statement affects. For more information about the WHEN clause, see «WHEN ( condition )».

When a trigger fires, tables that the trigger references might be undergoing changes made by SQL statements in other users’ transactions. SQL statements running in triggers follow the same rules that standalone SQL statements do. Specifically:

Queries in the trigger see the current read-consistent materialized view of referenced tables and any data changed in the same transaction.

Updates in the trigger wait for existing data locks to be released before proceeding.

A trigger is often called by the name of its triggering statement (for example, DELETE trigger or LOGON trigger ), the name of the item on which it is defined (for example, DATABASE trigger or SCHEMA trigger ), or its timing point (for example, BEFORE statement trigger or AFTER each row trigger ).

Reasons to Use Triggers

Triggers let you customize your database management system. For example, you can use triggers to:

Automatically generate calculated column values

Gather statistics on table access

Modify table data when DML statements are issued against views

Enforce referential integrity when child and parent tables are on different nodes of a distributed database

Publish information about database events, user events, and SQL statements to subscribing applications

Prevent DML operations on a table after regular business hours

Prevent invalid transactions

Enforce complex business or referential integrity rules that you cannot define with constraints (see «How Triggers and Constraints Differ»)

Triggers are not reliable security mechanisms, because they are programmatic and easy to disable. For high-assurance security, use Oracle Database Vault. For more information, see Oracle Database Vault Administrator’s Guide .

How Triggers and Constraints Differ

Both triggers and constraints can constrain data input, but they differ significantly.

A constraint applies to both existing and new data. For example, if a database column has a NOT NULL constraint, then its existing data is NOT NULL and no DML statement can violate the NOT NULL constraint.

A trigger applies only to new data. For example, a trigger can prevent a DML statement from inserting a NULL value into a database column, but the column might contain NULL values that were inserted into the column before the trigger was defined or while the trigger was disabled.

Constraints are easier to write and less error-prone than triggers that enforce the same rules. However, triggers can enforce some complex business rules that constraints cannot. Oracle strongly recommends that you use triggers to constrain data input only in these situations:

To enforce referential integrity when child and parent tables are on different nodes of a distributed database

To enforce complex business or referential integrity rules that you cannot define with constraints

Oracle Database Advanced Application Developer’s Guide for information about using constraints to enforce business rules and prevent the entry of invalid information into tables

«Triggers for Ensuring Referential Integrity» for information about using triggers and constraints to maintain referential integrity between parent and child tables

DML Triggers

A DML trigger is created on either a table or view, and its triggering event is composed of the DML statements DELETE , INSERT , and UPDATE . To create a trigger that fires in response to a MERGE statement, create triggers on the INSERT and UPDATE statements to which the MERGE operation decomposes.

A DML trigger is either simple or compound.

A simple DML trigger fires at exactly one of these timing points:

Before the triggering statement runs

(The trigger is called a BEFORE statement trigger or statement-level BEFORE trigger. )

After the triggering statement runs

(The trigger is called an AFTER statement trigger or statement-level AFTER trigger. )

Before each row that the triggering statement affects

(The trigger is called a BEFORE each row trigger or row-level BEFORE trigger. )

After each row that the triggering statement affects

(The trigger is called an AFTER each row trigger or row-level AFTER trigger. )

A compound DML trigger created on a table or editioning view can fire at one, some, or all of the preceding timing points. Compound DML triggers help program an approach where you want the actions that you implement for the various timing points to share common data. For more information, see «Compound DML Triggers».

A simple or compound DML trigger that fires at row level can access the data in the row that it is processing. For details, see «Correlation Names and Pseudorecords».

An INSTEAD OF trigger is a DML trigger created on a noneditioning view, or on a nested table column of a noneditioning view. The database fires the INSTEAD OF trigger instead of running the triggering DML statement. For more information, see «INSTEAD OF Triggers».

A crossedition trigger is a simple or compound DML trigger for use only in edition-based redefinition. For information about crossedition triggers, see Oracle Database Advanced Application Developer’s Guide .

Except in an INSTEAD OF trigger, a triggering UPDATE statement can include a column list. With a column list, the trigger fires only when a specified column is updated. Without a column list, the trigger fires when any column of the associated table is updated. For more information about the column list, see » dml_event_clause «.

Источник

9 PL/SQL Triggers

A trigger is like a stored procedure that Oracle Database invokes automatically whenever a specified event occurs.

The database can detect only system-defined events. You cannot define your own events.

Overview of Triggers

Like a stored procedure, a trigger is a named PL/SQL unit that is stored in the database and can be invoked repeatedly. Unlike a stored procedure, you can enable and disable a trigger, but you cannot explicitly invoke it. While a trigger is enabled , the database automatically invokes it—that is, the trigger fires —whenever its triggering event occurs. While a trigger is disabled , it does not fire.

You create a trigger with the CREATE TRIGGER statement. You specify the triggering event in terms of triggering statements and the item on which they act. The trigger is said to be created on or defined on the item, which is either a table, a view, a schema, or the database. You also specify the timing point , which determines whether the trigger fires before or after the triggering statement runs and whether it fires for each row that the triggering statement affects. By default, a trigger is created in the enabled state. For more information about the CREATE TRIGGER statement, see «CREATE TRIGGER Statement».

If the trigger is created on a table or view, then the triggering event is composed of DML statements, and the trigger is called a DML trigger . For more information, see «DML Triggers».

If the trigger is created on a schema or the database, then the triggering event is composed of either DDL or database operation statements, and the trigger is called a system trigger . For more information, see «System Triggers».

A conditional trigger has a WHEN clause that specifies a SQL condition that the database evaluates for each row that the triggering statement affects. For more information about the WHEN clause, see «WHEN ( condition )».

When a trigger fires, tables that the trigger references might be undergoing changes made by SQL statements in other users’ transactions. SQL statements running in triggers follow the same rules that standalone SQL statements do. Specifically:

Queries in the trigger see the current read-consistent materialized view of referenced tables and any data changed in the same transaction.

Updates in the trigger wait for existing data locks to be released before proceeding.

A trigger is often called by the name of its triggering statement (for example, DELETE trigger or LOGON trigger ), the name of the item on which it is defined (for example, DATABASE trigger or SCHEMA trigger ), or its timing point (for example, BEFORE statement trigger or AFTER each row trigger ).

Reasons to Use Triggers

Triggers let you customize your database management system. For example, you can use triggers to:

Automatically generate calculated column values

Gather statistics on table access

Modify table data when DML statements are issued against views

Enforce referential integrity when child and parent tables are on different nodes of a distributed database

Publish information about database events, user events, and SQL statements to subscribing applications

Prevent DML operations on a table after regular business hours

Prevent invalid transactions

Enforce complex business or referential integrity rules that you cannot define with constraints (see «How Triggers and Constraints Differ»)

Triggers are not reliable security mechanisms, because they are programmatic and easy to disable. For high-assurance security, use Oracle Database Vault, described in Oracle Database Vault Administrator’s Guide .

How Triggers and Constraints Differ

Both triggers and constraints can constrain data input, but they differ significantly.

A constraint applies to both existing and new data. For example, if a database column has a NOT NULL constraint, then its existing data is NOT NULL and no DML statement can violate the NOT NULL constraint.

A trigger applies only to new data. For example, a trigger can prevent a DML statement from inserting a NULL value into a database column, but the column might contain NULL values that were inserted into the column before the trigger was defined or while the trigger was disabled.

Constraints are easier to write and less error-prone than triggers that enforce the same rules. However, triggers can enforce some complex business rules that constraints cannot. Oracle strongly recommends that you use triggers to constrain data input only in these situations:

To enforce referential integrity when child and parent tables are on different nodes of a distributed database

To enforce complex business or referential integrity rules that you cannot define with constraints

Oracle Database Advanced Application Developer’s Guide for information about using constraints to enforce business rules and prevent the entry of invalid information into tables

«Triggers for Ensuring Referential Integrity» for information about using triggers and constraints to maintain referential integrity between parent and child tables

DML Triggers

A DML trigger is created on either a table or view, and its triggering event is composed of the DML statements DELETE , INSERT , and UPDATE . To create a trigger that fires in response to a MERGE statement, create triggers on the INSERT and UPDATE statements to which the MERGE operation decomposes.

A DML trigger is either simple or compound.

A simple DML trigger fires at exactly one of these timing points:

Before the triggering statement runs

(The trigger is called a BEFORE statement trigger or statement-level BEFORE trigger. )

After the triggering statement runs

(The trigger is called an AFTER statement trigger or statement-level AFTER trigger. )

Before each row that the triggering statement affects

(The trigger is called a BEFORE each row trigger or row-level BEFORE trigger. )

After each row that the triggering statement affects

(The trigger is called an AFTER each row trigger or row-level AFTER trigger. )

A compound DML trigger created on a table or editioning view can fire at one, some, or all of the preceding timing points. Compound DML triggers help program an approach where you want the actions that you implement for the various timing points to share common data. For more information, see «Compound DML Triggers».

A simple or compound DML trigger that fires at row level can access the data in the row that it is processing. For details, see «Correlation Names and Pseudorecords».

An INSTEAD OF trigger is a DML trigger created on a noneditioning view, or on a nested table column of a noneditioning view. The database fires the INSTEAD OF trigger instead of running the triggering DML statement. For more information, see «INSTEAD OF Triggers».

A crossedition trigger is a simple or compound DML trigger for use only in edition-based redefinition. For information about crossedition triggers, see Oracle Database Advanced Application Developer’s Guide .

Except in an INSTEAD OF trigger, a triggering UPDATE statement can include a column list. With a column list, the trigger fires only when a specified column is updated. Without a column list, the trigger fires when any column of the associated table is updated. For more information about the column list, see » dml_event_clause «.

READ  Traceback most recent call last import error

Источник

Raise application error in oracle trigger

A trigger is like a stored procedure that Oracle Database invokes automatically whenever a specified event occurs.

The database can detect only system-defined events. You cannot define your own events.

9.1 Overview of Triggers

Like a stored procedure, a trigger is a named PL/SQL unit that is stored in the database and can be invoked repeatedly. Unlike a stored procedure, you can enable and disable a trigger, but you cannot explicitly invoke it.

While a trigger is enabled , the database automatically invokes it—that is, the trigger fires —whenever its triggering event occurs. While a trigger is disabled , it does not fire.

You create a trigger with the CREATE TRIGGER statement. You specify the triggering event in terms of triggering statements and the item on which they act. The trigger is said to be created on or defined on the item, which is either a table, a view, a schema, or the database. You also specify the timing point , which determines whether the trigger fires before or after the triggering statement runs and whether it fires for each row that the triggering statement affects. By default, a trigger is created in the enabled state.

If the trigger is created on a table or view, then the triggering event is composed of DML statements, and the trigger is called a DML trigger .

A crossedition trigger is a DML trigger for use only in edition-based redefinition.

If the trigger is created on a schema or the database, then the triggering event is composed of either DDL or database operation statements, and the trigger is called a system trigger .

A conditional trigger is a DML or system trigger that has a WHEN clause that specifies a SQL condition that the database evaluates for each row that the triggering statement affects.

When a trigger fires, tables that the trigger references might be undergoing changes made by SQL statements in other users’ transactions. SQL statements running in triggers follow the same rules that standalone SQL statements do. Specifically:

Queries in the trigger see the current read-consistent materialized view of referenced tables and any data changed in the same transaction.

Updates in the trigger wait for existing data locks to be released before proceeding.

An INSTEAD OF trigger is either:

A DML trigger created on either a noneditioning view or a nested table column of a noneditioning view

A system trigger defined on a CREATE statement

The database fires the INSTEAD OF trigger instead of running the triggering statement.

A trigger is often called by the name of its triggering statement (for example, DELETE trigger or LOGON trigger ), the name of the item on which it is defined (for example, DATABASE trigger or SCHEMA trigger ), or its timing point (for example, BEFORE statement trigger or AFTER each row trigger ).

Oracle Database Development Guide for information about crossedition triggers

«CREATE TRIGGER Statement» for information about the WHEN clause

9.2 Reasons to Use Triggers

Triggers let you customize your database management system.

For example, you can use triggers to:

Automatically generate virtual column values

Gather statistics on table access

Modify table data when DML statements are issued against views

Enforce referential integrity when child and parent tables are on different nodes of a distributed database

Publish information about database events, user events, and SQL statements to subscribing applications

Prevent DML operations on a table after regular business hours

Prevent invalid transactions

Enforce complex business or referential integrity rules that you cannot define with constraints (see «How Triggers and Constraints Differ» )

Triggers are not reliable security mechanisms, because they are programmatic and easy to disable. For high-assurance security, use Oracle Database Vault, described in Oracle Database Vault Administrator’s Guide .

How Triggers and Constraints Differ

Both triggers and constraints can constrain data input, but they differ significantly.

A trigger always applies to new data only. For example, a trigger can prevent a DML statement from inserting a NULL value into a database column, but the column might contain NULL values that were inserted into the column before the trigger was defined or while the trigger was disabled.

A constraint can apply either to new data only (like a trigger) or to both new and existing data. Constraint behavior depends on constraint state, as explained in Oracle Database SQL Language Reference .

Constraints are easier to write and less error-prone than triggers that enforce the same rules. However, triggers can enforce some complex business rules that constraints cannot. Oracle strongly recommends that you use triggers to constrain data input only in these situations:

To enforce referential integrity when child and parent tables are on different nodes of a distributed database

To enforce complex business or referential integrity rules that you cannot define with constraints

Oracle Database Development Guide for information about using constraints to enforce business rules and prevent the entry of invalid information into tables

«Triggers for Ensuring Referential Integrity» for information about using triggers and constraints to maintain referential integrity between parent and child tables

9.3 DML Triggers

A DML trigger is created on either a table or view, and its triggering event is composed of the DML statements DELETE , INSERT , and UPDATE .

To create a trigger that fires in response to a MERGE statement, create triggers on the INSERT and UPDATE statements to which the MERGE operation decomposes.

A DML trigger is either simple or compound.

A simple DML trigger fires at exactly one of these timing points:

Before the triggering statement runs

(The trigger is called a BEFORE statement trigger or statement-level BEFORE trigger. )

After the triggering statement runs

(The trigger is called an AFTER statement trigger or statement-level AFTER trigger. )

Before each row that the triggering statement affects

(The trigger is called a BEFORE each row trigger or row-level BEFORE trigger. )

After each row that the triggering statement affects

(The trigger is called an AFTER each row trigger or row-level AFTER trigger. )

A compound DML trigger created on a table or editioning view can fire at one, some, or all of the preceding timing points. Compound DML triggers help program an approach where you want the actions that you implement for the various timing points to share common data.

A simple or compound DML trigger that fires at row level can access the data in the row that it is processing. For details, see «Correlation Names and Pseudorecords» .

An INSTEAD OF DML trigger is a DML trigger created on either a noneditioning view or a nested table column of a noneditioning view.

Except in an INSTEAD OF trigger, a triggering UPDATE statement can include a column list. With a column list, the trigger fires only when a specified column is updated. Without a column list, the trigger fires when any column of the associated table is updated.

9.3.1 Conditional Predicates for Detecting Triggering DML Statement

The triggering event of a DML trigger can be composed of multiple triggering statements. When one of them fires the trigger, the trigger can determine which one by using these conditional predicates .

Table 9-1 Conditional Predicates

An INSERT statement fired the trigger.

An UPDATE statement fired the trigger.

An UPDATE statement that affected the specified column fired the trigger.

A DELETE statement fired the trigger.

A conditional predicate can appear wherever a BOOLEAN expression can appear.

Example 9-1 Trigger Uses Conditional Predicates to Detect Triggering Statement

This example creates a DML trigger that uses conditional predicates to determine which of its four possible triggering statements fired it.

9.3.2 INSTEAD OF DML Triggers

An INSTEAD OF DML trigger is a DML trigger created on a noneditioning view, or on a nested table column of a noneditioning view. The database fires the INSTEAD OF trigger instead of running the triggering DML statement.

An INSTEAD OF trigger cannot be conditional.

An INSTEAD OF trigger is the only way to update a view that is not inherently updatable. Design the INSTEAD OF trigger to determine what operation was intended and do the appropriate DML operations on the underlying tables.

An INSTEAD OF trigger is always a row-level trigger. An INSTEAD OF trigger can read OLD and NEW values, but cannot change them.

An INSTEAD OF trigger with the NESTED TABLE clause fires only if the triggering statement operates on the elements of the specified nested table column of the view. The trigger fires for each modified nested table element.

Oracle Database SQL Language Reference for information about inherently updatable views

«Compound DML Trigger Structure» for information about compound DML triggers with the INSTEAD OF EACH ROW section

Example 9-2 INSTEAD OF Trigger

This example creates the view oe.order_info to display information about customers and their orders. The view is not inherently updatable (because the primary key of the orders table, order_id , is not unique in the result set of the join view). The example creates an INSTEAD OF trigger to process INSERT statements directed to the view. The trigger inserts rows into the base tables of the view, customers and orders .

Query to show that row to be inserted does not exist:

Insert row into view:

Query to show that row has been inserted in view:

Query to show that row has been inserted in customers table:

Query to show that row has been inserted in orders table:

Example 9-3 INSTEAD OF Trigger on Nested Table Column of View

In this example, the view dept_view contains a nested table of employees, emplist , created by the CAST function (described in Oracle Database SQL Language Reference ). To modify the emplist column, the example creates an INSTEAD OF trigger on the column.

Query view before inserting row into nested table:

Query table before inserting row into nested table:

Insert a row into nested table:

Query view after inserting row into nested table:

Result (formatted to fit page):

Query table after inserting row into nested table:

9.3.3 Compound DML Triggers

A compound DML trigger created on a table or editioning view can fire at multiple timing points. Each timing point section has its own executable part and optional exception-handling part, but all of these parts can access a common PL/SQL state. The common state is established when the triggering statement starts and is destroyed when the triggering statement completes, even when the triggering statement causes an error.

A compound DML trigger created on a noneditioning view is not really compound, because it has only one timing point section.

A compound trigger can be conditional, but not autonomous.

Two common uses of compound triggers are:

To accumulate rows destined for a second table so that you can periodically bulk-insert them

To avoid the mutating-table error (ORA-04091)

9.3.3.1 Compound DML Trigger Structure

The optional declarative part of a compound trigger declares variables and subprograms that all of its timing-point sections can use. When the trigger fires, the declarative part runs before any timing-point sections run. The variables and subprograms exist for the duration of the triggering statement.

A compound DML trigger created on a noneditioning view is not really compound, because it has only one timing point section. The syntax for creating the simplest compound DML trigger on a noneditioning view is:

A compound DML trigger created on a table or editioning view has at least one timing-point section in Table 9-2. If the trigger has multiple timing-point sections, they can be in any order, but no timing-point section can be repeated. If a timing-point section is absent, then nothing happens at its timing point.

Table 9-2 Compound Trigger Timing-Point Sections

Conditional Predicate TRUE if and only if:

Before the triggering statement runs

After the triggering statement runs

Before each row that the triggering statement affects

BEFORE EACH ROW

After each row that the triggering statement affects

«CREATE TRIGGER Statement» for more information about the syntax of compound triggers

A compound DML trigger does not have an initialization section, but the BEFORE STATEMENT section, which runs before any other timing-point section, can do any necessary initialization.

If a compound DML trigger has neither a BEFORE STATEMENT section nor an AFTER STATEMENT section, and its triggering statement affects no rows, then the trigger never fires.

9.3.3.2 Compound DML Trigger Restrictions

In addition to the «Trigger Restrictions» ), compound DML triggers have these restrictions:

OLD , NEW , and PARENT cannot appear in the declarative part, the BEFORE STATEMENT section, or the AFTER STATEMENT section.

Only the BEFORE EACH ROW section can change the value of NEW .

A timing-point section cannot handle exceptions raised in another timing-point section.

If a timing-point section includes a GOTO statement, the target of the GOTO statement must be in the same timing-point section.

9.3.3.3 Performance Benefit of Compound DML Triggers

A compound DML trigger has a performance benefit when the triggering statement affects many rows.

For example, suppose that this statement triggers a compound DML trigger that has all four timing-point sections in Table 9-2:

Although the BEFORE EACH ROW and AFTER EACH ROW sections of the trigger run for each row of Source whose column c1 is greater than zero, the BEFORE STATEMENT section runs only before the INSERT statement runs and the AFTER STATEMENT section runs only after the INSERT statement runs.

A compound DML trigger has a greater performance benefit when it uses bulk SQL, described in «Bulk SQL and Bulk Binding» .

9.3.3.4 Using Compound DML Triggers with Bulk Insertion

A compound DML trigger is useful for accumulating rows destined for a second table so that you can periodically bulk-insert them. To get the performance benefit from the compound trigger, you must specify BULK COLLECT INTO in the FORALL statement (otherwise, the FORALL statement does a single-row DML operation multiple times). For more information about using the BULK COLLECT clause with the FORALL statement, see «Using FORALL Statement and BULK COLLECT Clause Together» .

Scenario: You want to log every change to hr . employees . salary in a new table, employee_salaries . A single UPDATE statement updates many rows of the table hr . employees ; therefore, bulk-inserting rows into employee . salaries is more efficient than inserting them individually.

Solution: Define a compound trigger on updates of the table hr . employees , as in Example 9-4. You do not need a BEFORE STATEMENT section to initialize idx or salaries , because they are state variables, which are initialized each time the trigger fires (even when the triggering statement is interrupted and restarted).

To run Example 9-4, you must have the EXECUTE privilege on the package DBMS_LOCK .

Example 9-4 Compound Trigger Logs Changes to One Table in Another Table

Increase salary of every employee in department 50 by 10%:

Wait two seconds:

Increase salary of every employee in department 50 by 5%:

See changes to employees table reflected in employee_salaries table:

9.3.3.5 Using Compound DML Triggers to Avoid Mutating-Table Error

A compound DML trigger is useful for avoiding the mutating-table error (ORA-04091) explained in «Mutating-Table Restriction» .

Scenario: A business rule states that an employee’s salary increase must not exceed 10% of the average salary for the employee’s department. This rule must be enforced by a trigger.

Solution: Define a compound trigger on updates of the table hr . employees , as in Example 9-5. The state variables are initialized each time the trigger fires (even when the triggering statement is interrupted and restarted).

Example 9-5 Compound Trigger Avoids Mutating-Table Error

9.3.4 Triggers for Ensuring Referential Integrity

You can use triggers and constraints to maintain referential integrity between parent and child tables, as Table 9-3 shows. (For more information about constraints, see Oracle Database SQL Language Reference .)

Table 9-3 Constraints and Triggers for Ensuring Referential Integrity

Timing Point Section

PRIMARY KEY or UNIQUE

One or more triggers that ensure that when PRIMARY KEY or UNIQUE values are updated or deleted, the desired action ( RESTRICT , CASCADE , or SET NULL ) occurs on corresponding FOREIGN KEY values.

No action is required for inserts into the parent table, because no dependent foreign keys exist.

FOREIGN KEY , if parent and child are in the same database. (The database does not support declarative referential constraints between tables on different nodes of a distributed database.)

Disable this foreign key constraint to prevent the corresponding PRIMARY KEY or UNIQUE constraint from being dropped (except explicitly with the CASCADE option).

One trigger that ensures that values inserted or updated in the FOREIGN KEY correspond to PRIMARY KEY or UNIQUE values in the parent table.

The examples in the following topics use these tables, which share the column Deptno :

Several triggers include statements that lock rows ( SELECT FOR UPDATE ). This operation is necessary to maintain concurrency while the rows are being processed.

These examples are not meant to be used exactly as written. They are provided to assist you in designing your own triggers.

9.3.4.1 Foreign Key Trigger for Child Table

The trigger in Example 9-6 ensures that before an INSERT or UPDATE statement affects a foreign key value, the corresponding value exists in the parent key. The exception ORA-04091 (mutating-table error) allows the trigger emp_dept_check to be used with the UPDATE_SET_DEFAULT and UPDATE_CASCADE triggers. This exception is unnecessary if the trigger emp_dept_check is used alone.

Example 9-6 Foreign Key Trigger for Child Table

9.3.4.2 UPDATE and DELETE RESTRICT Trigger for Parent Table

The trigger in Example 9-7 enforces the UPDATE and DELETE RESTRICT referential action on the primary key of the dept table.

The trigger in Example 9-7 does not work with self-referential tables (tables with both the primary/unique key and the foreign key). Also, this trigger does not allow triggers to cycle (such as when A fires B, which fires A).

Example 9-7 UPDATE and DELETE RESTRICT Trigger for Parent Table

9.3.4.3 UPDATE and DELETE SET NULL Trigger for Parent Table

The trigger in Example 9-8 enforces the UPDATE and DELETE SET NULL referential action on the primary key of the dept table.

Example 9-8 UPDATE and DELETE SET NULL Trigger for Parent Table

9.3.4.4 DELETE CASCADE Trigger for Parent Table

The trigger in Example 9-9 enforces the DELETE CASCADE referential action on the primary key of the dept table.

Typically, the code for DELETE CASCADE is combined with the code for UPDATE SET NULL or UPDATE SET DEFAULT , to account for both updates and deletes.

Example 9-9 DELETE CASCADE Trigger for Parent Table

9.3.4.5 UPDATE CASCADE Trigger for Parent Table

The triggers in Example 9-10 ensure that if a department number is updated in the dept table, then this change is propagated to dependent foreign keys in the emp table.

Because the trigger dept_cascade2 updates the emp table, the emp_dept_check trigger in Example 9-6, if enabled, also fires. The resulting mutating-table error is trapped by the emp_dept_check trigger. Carefully test any triggers that require error trapping to succeed to ensure that they always work properly in your environment.

Example 9-10 UPDATE CASCADE Trigger for Parent Table

9.3.4.6 Triggers for Complex Constraint Checking

Triggers can enforce integrity rules other than referential integrity. The trigger in Example 9-11 does a complex check before allowing the triggering statement to run.

Example 9-11 needs this data structure:

Example 9-11 Trigger Checks Complex Constraints

9.3.4.7 Triggers for Complex Security Authorizations

Triggers are commonly used to enforce complex security authorizations for table data. Use triggers only to enforce complex security authorizations that you cannot define using the database security features provided with the database. For example, use a trigger to prohibit updates to the employee table during weekends and nonworking hours.

When using a trigger to enforce a complex security authorization, it is best to use a BEFORE statement trigger. Using a BEFORE statement trigger has these benefits:

The security check is done before the triggering statement is allowed to run, so that no wasted work is done by an unauthorized statement.

The security check is done only for the triggering statement, not for each row affected by the triggering statement.

The trigger in Example 9-12 enforces security by raising exceptions when anyone tries to update the table employees during weekends or nonworking hours.

Oracle Database Security Guide for detailed information about database security features

Example 9-12 Trigger Enforces Security Authorizations

9.3.4.8 Triggers for Transparent Event Logging

Triggers are very useful when you want to transparently do a related change in the database following certain events.

The REORDER trigger example shows a trigger that reorders parts as necessary when certain conditions are met. (In other words, a triggering statement is entered, and the PARTS_ON_HAND value is less than the REORDER_POINT value.)

9.3.4.9 Triggers for Deriving Column Values

Triggers can derive column values automatically, based upon a value provided by an INSERT or UPDATE statement. This type of trigger is useful to force values in specific columns that depend on the values of other columns in the same row. BEFORE row triggers are necessary to complete this type of operation for these reasons:

The dependent values must be derived before the INSERT or UPDATE occurs, so that the triggering statement can use the derived values.

The trigger must fire for each row affected by the triggering INSERT or UPDATE statement.

The trigger in Example 9-13 derives new column values for a table whenever a row is inserted or updated.

Example 9-13 needs this change to this data structure:

Example 9-13 Trigger Derives New Column Values

9.3.4.10 Triggers for Building Complex Updatable Views

Views are an excellent mechanism to provide logical windows over table data. However, when the view query gets complex, the system implicitly cannot translate the DML on the view into those on the underlying tables. INSTEAD OF triggers help solve this problem. These triggers can be defined over views, and they fire instead of the actual DML.

Consider a library system where books are arranged by title. The library consists of a collection of book type objects:

The table Book_table is created and populated like this:

The table Library_table is created and populated like this:

You can define a complex view over the tables Book_table and Library_table to create a logical view of the library with sections and a collection of books in each section:

(For information about the CAST function, see Oracle Database SQL Language Reference .)

Make Library_view updatable by defining an INSTEAD OF trigger on it:

Insert a new row into Library_view :

See the effect on Library_view :

See the effect on Book_table :

See the effect on Library_table :

Similarly, you can also define triggers on the nested table booklist to handle modification of the nested table element.

9.3.4.11 Triggers for Fine-Grained Access Control

You can use LOGON triggers to run the package associated with an application context. An application context captures session-related information about the user who is logging in to the database. From there, your application can control how much access this user has, based on his or her session information.

If you have very specific logon requirements, such as preventing users from logging in from outside the firewall or after work hours, consider using Oracle Database Vault instead of LOGON triggers. With Oracle Database Vault, you can create custom rules to strictly control user access.

Oracle Database Security Guide for information about creating a LOGON trigger to run a database session application context package

Oracle Database Vault Administrator’s Guide for information about Oracle Database Vault

9.4 Correlation Names and Pseudorecords

This topic applies only to triggers that fire at row level. That is:

Row-level simple DML triggers

Compound DML triggers with row-level timing point sections

A trigger that fires at row level can access the data in the row that it is processing by using correlation names . The default correlation names are OLD , NEW , and PARENT . To change the correlation names, use the REFERENCING clause of the CREATE TRIGGER statement (see «referencing_clause ::=» ).

If the trigger is created on a nested table, then OLD and NEW refer to the current row of the nested table, and PARENT refers to the current row of the parent table. If the trigger is created on a table or view, then OLD and NEW refer to the current row of the table or view, and PARENT is undefined.

OLD , NEW , and PARENT are also called pseudorecords , because they have record structure, but are allowed in fewer contexts than records are. The structure of a pseudorecord is table_name %ROWTYPE , where table_name is the name of the table on which the trigger is created (for OLD and NEW ) or the name of the parent table (for PARENT ).

In the trigger_body of a simple trigger or the tps_body of a compound trigger, a correlation name is a placeholder for a bind variable. Reference the field of a pseudorecord with this syntax:

In the WHEN clause of a conditional trigger, a correlation name is not a placeholder for a bind variable. Therefore, omit the colon in the preceding syntax.

Table 9-4 shows the values of OLD and NEW fields for the row that the triggering statement is processing.

Table 9-4 OLD and NEW Pseudorecord Field Values

Table Constraint to Declare on Table Triggers to Create on Table

The restrictions on pseudorecords are:

A pseudorecord cannot appear in a record-level operation.

For example, the trigger cannot include this statement:

A pseudorecord cannot be an actual subprogram parameter.

(A pseudorecord field can be an actual subprogram parameter.)

The trigger cannot change OLD field values.

Trying to do so raises ORA-04085.

If the triggering statement is DELETE , then the trigger cannot change NEW field values.

Trying to do so raises ORA-04084.

An AFTER trigger cannot change NEW field values, because the triggering statement runs before the trigger fires.

Trying to do so raises ORA-04084.

A BEFORE trigger can change NEW field values before a triggering INSERT or UPDATE statement puts them in the table.

If a statement triggers both a BEFORE trigger and an AFTER trigger, and the BEFORE trigger changes a NEW field value, then the AFTER trigger «sees» that change.

Example 9-14 Trigger Logs Changes to EMPLOYEES.SALARY

This example creates a log table and a trigger that inserts a row in the log table after any UPDATE statement affects the SALARY column of the EMPLOYEES table, and then updates EMPLOYEES . SALARY and shows the log table.

Create log table:

Create trigger that inserts row in log table after EMPLOYEES . SALARY is updated:

Update EMPLOYEES . SALARY :

Example 9-15 Conditional Trigger Prints Salary Change Information

This example creates a conditional trigger that prints salary change information whenever a DELETE , INSERT , or UPDATE statement affects the EMPLOYEES table—unless that information is about the President. The database evaluates the WHEN condition for each affected row. If the WHEN condition is TRUE for an affected row, then the trigger fires for that row before the triggering statement runs. If the WHEN condition is not TRUE for an affected row, then trigger does not fire for that row, but the triggering statement still runs.

Example 9-16 Trigger Modifies CLOB Columns

This example creates an UPDATE trigger that modifies CLOB columns.

For information about TO_CLOB and other conversion functions, see Oracle Database SQL Language Reference .

Example 9-17 Trigger with REFERENCING Clause

This example creates a table with the same name as a correlation name, new , and then creates a trigger on that table. To avoid conflict between the table name and the correlation name, the trigger references the correlation name as Newest .

9.4.1 OBJECT_VALUE Pseudocolumn

A DML trigger on an object table can reference the SQL pseudocolumn OBJECT_VALUE , which returns system-generated names for the columns of the object table. The trigger can also invoke a PL/SQL subprogram that has a formal IN parameter whose data type is OBJECT_VALUE .

Oracle Database SQL Language Reference for more information about OBJECT_VALUE

Oracle Database SQL Language Reference for general information about pseudocolumns

Example 9-18 creates object table tbl , table tbl_history for logging updates to tbl , and trigger Tbl_Trg . The trigger runs for each row of tb1 that is affected by a DML statement, causing the old and new values of the object t in tbl to be written in tbl_history . The old and new values are : OLD . OBJECT_VALUE and : NEW . OBJECT_VALUE .

All values of column n were increased by 1. The value of m remains 0.

Example 9-18 Trigger References OBJECT_VALUE Pseudocolumn

Create, populate, and show object table:

Create history table and trigger:

Update object table:

Show old and new values:

9.5 System Triggers

A system trigger is created on either a schema or the database.

Its triggering event is composed of either DDL statements (listed in «ddl_event» ) or database operation statements (listed in «database_event» ).

A system trigger fires at exactly one of these timing points:

Before the triggering statement runs

(The trigger is called a BEFORE statement trigger or statement-level BEFORE trigger. )

After the triggering statement runs

(The trigger is called a AFTER statement trigger or statement-level AFTER trigger. )

Instead of the triggering CREATE statement

(The trigger is called an INSTEAD OF CREATE trigger .)

9.5.1 SCHEMA Triggers

A SCHEMA trigger is created on a schema and fires whenever the user who owns it is the current user and initiates the triggering event.

Suppose that both user1 and user2 own schema triggers, and user1 invokes a DR unit owned by user2. Inside the DR unit, user2 is the current user. Therefore, if the DR unit initiates the triggering event of a schema trigger that user2 owns, then that trigger fires. However, if the DR unit initiates the triggering event of a schema trigger that user1 owns, then that trigger does not fire.

Example 9-19 creates a BEFORE statement trigger on the sample schema HR . When a user connected as HR tries to drop a database object, the database fires the trigger before dropping the object.

Example 9-19 BEFORE Statement Trigger on Sample Schema HR

9.5.2 DATABASE Triggers

A DATABASE trigger is created on the database and fires whenever any database user initiates the triggering event.

Example 9-20 shows the basic syntax for a trigger to log errors. This trigger fires after an unsuccessful statement execution, such as unsuccessful logon.

An AFTER SERVERERROR trigger fires only if Oracle relational database management system (RDBMS) determines that it is safe to fire error triggers. For more information about AFTER SERVERERROR triggers, see CREATE TRIGGER Statement.

The trigger in Example 9-21 runs the procedure check_user after a user logs onto the database.

Example 9-20 AFTER Statement Trigger on Database

Example 9-21 Trigger Monitors Logons

9.5.3 INSTEAD OF CREATE Triggers

An INSTEAD OF CREATE trigger is a SCHEMA trigger whose triggering event is a CREATE statement. The database fires the trigger instead of executing its triggering statement.

Example 9-22 shows the basic syntax for an INSTEAD OF CREATE trigger on the current schema. This trigger fires when the owner of the current schema issues a CREATE statement in the current schema.

Example 9-22 INSTEAD OF CREATE Trigger on Schema

9.6 Subprograms Invoked by Triggers

Triggers can invoke subprograms written in PL/SQL, C, and Java. The trigger in Example 9-4 invokes a PL/SQL subprogram. The trigger in Example 9-23 invokes a Java subprogram.

A subprogram invoked by a trigger cannot run transaction control statements, because the subprogram runs in the context of the trigger body.

If a trigger invokes an invoker rights (IR) subprogram, then the user who created the trigger, not the user who ran the triggering statement, is considered to be the current user. For information about IR subprograms, see «Invoker’s Rights and Definer’s Rights (AUTHID Property)» .

If a trigger invokes a remote subprogram, and a time stamp or signature mismatch is found during execution of the trigger, then the remote subprogram does not run and the trigger is invalidated.

Example 9-23 Trigger Invokes Java Subprogram

The corresponding Java file is thjvTriggers . java :

9.7 Trigger Compilation, Invalidation, and Recompilation

The CREATE TRIGGER statement compiles the trigger and stores its code in the database. If a compilation error occurs, the trigger is still created, but its triggering statement fails, except in these cases:

The trigger was created in the disabled state.

The triggering event is AFTER STARTUP ON DATABASE .

The triggering event is either AFTER LOGON ON DATABASE or AFTER LOGON ON SCHEMA , and someone logs on as SYSTEM .

To see trigger compilation errors, either use the SHOW ERRORS command in SQL*Plus or Enterprise Manager, or query the static data dictionary view *_ERRORS (described in Oracle Database Reference ).

If a trigger does not compile successfully, then its exception handler cannot run. For an example, see «Remote Exception Handling» .

If a trigger references another object, such as a subprogram or package, and that object is modified or dropped, then the trigger becomes invalid. The next time the triggering event occurs, the compiler tries to revalidate the trigger (for details, see Oracle Database Development Guide ).

Because the DBMS_AQ package is used to enqueue a message, dependency between triggers and queues cannot be maintained.

To recompile a trigger manually, use the ALTER TRIGGER statement, described in «ALTER TRIGGER Statement» .

9.8 Exception Handling in Triggers

In most cases, if a trigger runs a statement that raises an exception, and the exception is not handled by an exception handler, then the database rolls back the effects of both the trigger and its triggering statement.

In the following cases, the database rolls back only the effects of the trigger, not the effects of the triggering statement (and logs the error in trace files and the alert log):

The triggering event is either AFTER STARTUP ON DATABASE or BEFORE SHUTDOWN ON DATABASE .

The triggering event is AFTER LOGON ON DATABASE and the user has the ADMINISTER DATABASE TRIGGER privilege.

The triggering event is AFTER LOGON ON SCHEMA and the user either owns the schema or has the ALTER ANY TRIGGER privilege.

In the case of a compound DML trigger, the database rolls back only the effects of the triggering statement, not the effects of the trigger. However, variables declared in the trigger are re-initialized, and any values computed before the triggering statement was rolled back are lost.

Triggers that enforce complex security authorizations or constraints typically raise user-defined exceptions, which are explained in «User-Defined Exceptions» .

PL/SQL Error Handling, for general information about exception handling

Remote Exception Handling

A trigger that accesses a remote database can do remote exception handling only if the remote database is available. If the remote database is unavailable when the local database must compile the trigger, then the local database cannot validate the statement that accesses the remote database, and the compilation fails. If the trigger cannot be compiled, then its exception handler cannot run.

The trigger in Example 9-24 has an INSERT statement that accesses a remote database. The trigger also has an exception handler. However, if the remote database is unavailable when the local database tries to compile the trigger, then the compilation fails and the exception handler cannot run.

Example 9-25 shows the workaround for the problem in Example 9-24: Put the remote INSERT statement and exception handler in a stored subprogram and have the trigger invoke the stored subprogram. The subprogram is stored in the local database in compiled form, with a validated statement for accessing the remote database. Therefore, when the remote INSERT statement fails because the remote database is unavailable, the exception handler in the subprogram can handle it.

Example 9-24 Trigger Cannot Handle Exception if Remote Database is Unavailable

Example 9-25 Workaround for Example 9-24

9.9 Trigger Design Guidelines

Use triggers to ensure that whenever a specific event occurs, any necessary actions are done (regardless of which user or application issues the triggering statement).

For example, use a trigger to ensure that whenever anyone updates a table, its log file is updated.

Do not create triggers that duplicate database features.

For example, do not create a trigger to reject invalid data if you can do the same with constraints (see «How Triggers and Constraints Differ» ).

Do not create triggers that depend on the order in which a SQL statement processes rows (which can vary).

For example, do not assign a value to a global package variable in a row trigger if the current value of the variable depends on the row being processed by the row trigger. If a trigger updates global package variables, initialize those variables in a BEFORE statement trigger.

Use BEFORE row triggers to modify the row before writing the row data to disk.

Use AFTER row triggers to obtain the row ID and use it in operations.

An AFTER row trigger fires when the triggering statement results in ORA-02292.

AFTER row triggers are slightly more efficient than BEFORE row triggers. With BEFORE row triggers, affected data blocks are read first for the trigger and then for the triggering statement. With AFTER row triggers, affected data blocks are read only for the trigger.

If the triggering statement of a BEFORE statement trigger is an UPDATE or DELETE statement that conflicts with an UPDATE statement that is running, then the database does a transparent ROLLBACK to SAVEPOINT and restarts the triggering statement. The database can do this many times before the triggering statement completes successfully. Each time the database restarts the triggering statement, the trigger fires. The ROLLBACK to SAVEPOINT does not undo changes to package variables that the trigger references. To detect this situation, include a counter variable in the package.

Do not create recursive triggers.

For example, do not create an AFTER UPDATE trigger that issues an UPDATE statement on the table on which the trigger is defined. The trigger fires recursively until it runs out of memory.

If you create a trigger that includes a statement that accesses a remote database, then put the exception handler for that statement in a stored subprogram and invoke the subprogram from the trigger.

Use DATABASE triggers judiciously. They fire every time any database user initiates a triggering event.

If a trigger runs the following statement, the statement returns the owner of the trigger, not the user who is updating the table:

Only committed triggers fire.

A trigger is committed, implicitly, after the CREATE TRIGGER statement that creates it succeeds. Therefore, the following statement cannot fire the trigger that it creates:

To allow the modular installation of applications that have triggers on the same tables, create multiple triggers of the same type, rather than a single trigger that runs a sequence of operations.

Each trigger sees the changes made by the previously fired triggers. Each trigger can see OLD and NEW values.

9.10 Trigger Restrictions

In addition to the restrictions that apply to all PL/SQL units (see Table C-1), triggers have these restrictions:

Only an autonomous trigger can run TCL or DDL statements.

For information about autonomous triggers, see «Autonomous Triggers» .

A trigger cannot invoke a subprogram that runs transaction control statements, because the subprogram runs in the context of the trigger body.

For more information about subprograms invoked by triggers, see «Subprograms Invoked by Triggers» .

A trigger cannot access a SERIALLY_REUSABLE package.

For information about SERIALLY_REUSABLE packages, see «SERIALLY_REUSABLE Packages» .

9.10.1 Trigger Size Restriction

The size of the trigger cannot exceed 32K.

If the logic for your trigger requires much more than 60 lines of PL/SQL source text, then put most of the source text in a stored subprogram and invoke the subprogram from the trigger. For information about subprograms invoked by triggers, see «Subprograms Invoked by Triggers» .

9.10.2 Trigger LONG and LONG RAW Data Type Restrictions

Oracle supports the LONG and LONG RAW data types only for backward compatibility with existing applications.

In addition to the restrictions that apply to all PL/SQL units (see «LONG and LONG RAW Variables» ), triggers have these restrictions:

A trigger cannot declare a variable of the LONG or LONG RAW data type.

A SQL statement in a trigger can reference a LONG or LONG RAW column only if the column data can be converted to the data type CHAR or VARCHAR2 .

A trigger cannot use the correlation name NEW or PARENT with a LONG or LONG RAW column.

9.10.3 Mutating-Table Restriction

This topic applies only to row-level simple DML triggers.

A mutating table is a table that is being modified by a DML statement (possibly by the effects of a DELETE CASCADE constraint). (A view being modified by an INSTEAD OF trigger is not considered to be mutating.)

The mutating-table restriction prevents the trigger from querying or modifying the table that the triggering statement is modifying. When a row-level trigger encounters a mutating table, ORA-04091 occurs, the effects of the trigger and triggering statement are rolled back, and control returns to the user or application that issued the triggering statement, as Example 9-26 shows.

Oracle Database does not enforce the mutating-table restriction for a trigger that accesses remote nodes, because the database does not support declarative referential constraints between tables on different nodes of a distributed database.

Similarly, the database does not enforce the mutating-table restriction for tables in the same database that are connected by loop-back database links. A loop-back database link makes a local table appear remote by defining an Oracle Net path back to the database that contains the link.

If you must use a trigger to update a mutating table, you can avoid the mutating-table error in either of these ways:

Use a temporary table.

For example, instead of using one AFTER each row trigger that updates the mutating table, use two triggers—an AFTER each row trigger that updates the temporary table and an AFTER statement trigger that updates the mutating table with the values from the temporary table.

Mutating-Table Restriction Relaxed

As of Oracle Database 8 g Release 1, a deletion from the parent table causes BEFORE and AFTER triggers to fire once. Therefore, you can create row-level and statement-level triggers that query and modify the parent and child tables. This allows most foreign key constraint actions to be implemented through their after-row triggers (unless the constraint is self-referential). Update cascade, update set null, update set default, delete set default, inserting a missing parent, and maintaining a count of children can all be implemented easily—see «Triggers for Ensuring Referential Integrity» .

However, cascades require care for multiple-row foreign key updates. The trigger cannot miss rows that were changed but not committed by another transaction, because the foreign key constraint guarantees that no matching foreign key rows are locked before the after-row trigger is invoked.

In Example 9-27, the triggering statement updates p correctly but causes problems when the trigger updates f . First, the triggering statement changes (1) to (2) in p , and the trigger updates (1) to (2) in f , leaving two rows of value (2) in f . Next, the triggering statement updates (2) to (3) in p , and the trigger updates both rows of value (2) to (3) in f . Finally, the statement updates (3) to (4) in p , and the trigger updates all three rows in f from (3) to (4). The relationship between the data items in p and f is lost.

To avoid this problem, either forbid multiple-row updates to p that change the primary key and reuse existing primary key values, or track updates to foreign key values and modify the trigger to ensure that no row is updated twice.

Example 9-26 Trigger Causes Mutating-Table Error

Show that effect of trigger was rolled back:

Show that effect of triggering statement was rolled back:

Источник

READ  Python built in exception
Smartadm.ru
Triggering Statement OLD.field Value NEW.field Value