Database Administrator’s Guide. Contents. Previous Next. Page of Search. This Book This Release. Table of Contents. open Oracle Database. Overview of Triggers. A trigger is a named program unit that is stored in the database and fired (executed) in response to a specified event. The specified event. Prerequisites. The trigger must be in your own schema or you must have ALTER ANY TRIGGER system privilege. In addition, to alter a trigger on DATABASE.
|Published (Last):||3 November 2015|
|PDF File Size:||11.98 Mb|
|ePub File Size:||15.18 Mb|
|Price:||Free* [*Free Regsitration Required]|
Triggers are procedures that are stored in the database and implicitly run, or firedwhen something happens.
Enabling and Disabling Triggers
You can choose what combination of operations should fire the trigger. Because the trigger uses the BEFORE keyword, it can access the new values before they go into the diaparadores, and can change the values if there is an easily-corrected error by assigning to: You might use the AFTER keyword if you want the trigger to query or change the same table, disparacores triggers can only do that after the initial changes are applied and the table is back in a consistent state.
You might omit this clause if you just want to record the fact that the operation occurred, but not examine the data disparadoree each row. The following sections use this example to illustrate the way that parts of a trigger are specified. Oracle Database automatically executes a trigger when a specified event takes place, which may be in the form of a system event or a DML statement being issued against the table. Creating a trigger on SCHEMA implies that the trigger is created in the current user’s schema and is fired only for that user.
Trigger names must be unique with respect to other triggers in the same schema. Trigger names do not need to be unique with respect to other schema objects, such as tables, views, and procedures. For example, a table and a trigger can have the same name however, to avoid confusion, this is not recommended. A trigger is fired based on a triggering statementwhich specifies:.
Exactly one table or view can be specified in the triggering statement. For direct loads, triggers are disabled before the load. If a triggering statement includes a column list, the trigger is fired only when one of the specified columns is updated. If a triggering dipsaradores omits a column list, the trigger is fired when any column of the associated table is updated.
With AFTER row triggers, affected data blocks must be read logical read, not physical read once for the trigger and then again for the triggering statement. Alternatively, with BEFORE row triggers, the data blocks must be read only once for both the triggering statement and the trigger. This can occur many times before the statement completes successfully. The rollback to savepoint does not undo changes to any package variables referenced in the trigger.
Your package should include a counter variable to detect this situation. A lracle database does not guarantee the order of rows processed disparadorew a SQL statement.
Therefore, do not create triggers that depend oralce the order in which rows disparadors processed. For example, do not assign a value to a global package variable in a row trigger if the current value of the global variable is dependent on the row being processed by the row trigger. Also, if global package variables are updated within a trigger, then it is disparadorez to initialize those variables in a BEFORE statement trigger. When a statement in a trigger body causes another trigger to be fired, the triggers are said to be cascading.
Oracle Database allows up to 32 triggers to cascade at any one time. Although any trigger can run disparxdores sequence of operations either in-line or by calling procedures, using multiple triggers of the same type enhances database administration by permitting the modular installation of applications dispadadores have triggers on the same tables.
Oracle Database executes all triggers of the same type before executing triggers of a different type. If you have multiple triggers of the same type on a single table, then Oracle Database chooses an disparadorse order to execute these triggers.
Dispaeadores Database Concepts for more information on the firing order of triggers. Each subsequent trigger sees the changes made by the previously fired triggers. Each trigger can see the old and new values.
To ensure that multiple triggered actions occur cisparadores a specific order, you must consolidate these actions into a single trigger for example, by having the trigger call a series of procedures.
An updatable view is one that lets you perform DML on dusparadores underlying table. If a view contains pseudocolumns or expressions, then you can only update the view with an UPDATE statement that does not refer to any of the pseudocolumns or expressions. The actions then insert new rows or update existing rows, as appropriate. Oracle Call Interface Programmer’s Guide. To modify an object materialized by an object view in the client-side disparadorees cache and flush it back to the persistent store, you must specify INSTEAD OF triggers, unless the object view is modifiable.
If the object is read only, then it is not necessary to define triggers to pin it. These triggers provide a way of updating elements of the nested table.
Enabling and Disabling Triggers
They fire for each nested table element being modified. The row correlation variables inside the trigger correspond to the nested table element. This type of trigger also provides an additional correlation name for accessing the parent row that contains the nested table being modified. If there are five employees in department 20, then the trigger fires five times when this statement is entered, because five rows are affected. Oracle Database Concepts for the order of trigger firing.
The statement level triggers are useful for performing validation checks for the entire statement. A WHEN clause cannot be included in the definition of a statement trigger. If included, then the expression in the WHEN clause is evaluated for each row that the trigger affects. If the expression evaluates to TRUE for a row, then the trigger body is fired on behalf of that row.
In more realistic examples, you might test if one column value is less than another. The expression in a WHEN clause of a row trigger can include correlation names, which xisparadores explained later. These statements are run if the triggering statement is entered and if the trigger restriction if included evaluates to TRUE.
You may need to set up data structures similar to the following for certain examples to work:. Two correlation names exist for every column of the dsiparadores being modified: Depending on the type of triggering statement, certain correlation names might not have any meaning. The new column values are referenced using the new qualifier before the column name, while orac,e old column values are referenced using the old qualifier before the column name.
The parent row corresponding to this nested table element can be accessed using the parent qualifier.
The parent correlation name is meaningful and valid only inside a nested table trigger. Because this is rare, this option is infrequently used. For example, assume you have a table named new with columns field1 number and field2 character.
Within the code of the trigger body, you can execute blocks of code depending on the kind of DML operation fired the trigger:. For example, assume a trigger is defined as the following:. This way, the trigger can minimize its overhead when the column of interest is not being changed. If a predefined or user-defined error condition or exception is raised during the execution of a trigger body, then all effects of the trigger body, as well as the triggering statement, are rolled back unless the error is trapped by an exception handler.
Therefore, a trigger body can prevent the execution of the triggering statement by raising an exception. User-defined exceptions are commonly used in triggers that enforce complex security authorizations or integrity constraints.
In these scenarios, only the trigger action is rolled back. A trigger that accesses a remote site cannot do remote exception handling if the network link is unavailable. A trigger is compiled when it is created.
Thus, if a remote site is unavailable when the trigger must compile, then Oracle Database cannot validate the statement accessing the remote database, and the compilation fails. The previous example exception statement cannot run, because the trigger does not complete compilation.
Because stored procedures are stored in a compiled form, the work-around for the previous example is as follows:. The trigger in this example compiles successfully and calls the stored procedure, which already has a validated statement for accessing the remote database; thus, when the remote INSERT statement fails because the link is down, the exception is caught. The following sections discuss these restrictions. DDL statements are not allowed in the body of a trigger.
Also, no transaction control statements are allowed in a trigger. A procedure called by a trigger cannot run the previous transaction control statements, because the procedure runs within the context of the trigger body. Statements inside a trigger can reference remote schema objects. However, pay special attention when calling remote procedures from within a dispzradores trigger. If a timestamp or signature mismatch is found during execution of the trigger, then the remote ogacle is not run, and the trigger is invalidated.
The session dispardores issued the triggering statement cannot query or modify a mutating table. This restriction prevents a trigger from seeing an inconsistent set disparadpres data. When a trigger encounters a mutating table, a runtime error occurs, the effects of dispqradores trigger body and triggering statement are rolled back, and oraxle is returned to the user or application.
For example, in place of a single AFTER row trigger that updates the original table, resulting in a mutating table error, you might use two triggers–an AFTER row trigger that updates a temporary table, and an AFTER statement trigger that updates the original table with the values from the temporary table.
Declarative orscle constraints are checked at various times with respect to row triggers. Oracle Database Concepts for information about the interaction of triggers and integrity constraints.
Because declarative referential integrity constraints are not supported between tables on different nodes of a distributed database, the mutating table restrictions do not apply to triggers that access remote nodes.
These restrictions are also not oraclee among 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. The mutating error, discussed earlier in this section, still prevents the trigger from reading or modifying the table that the parent statement is modifying.
9 Using Triggers
However, starting in Oracle Database release 8. Oeacle way, you can create triggers just not row triggers to read and modify the parent and child tables. This allows most foreign key constraint actions to be implemented through their obvious after-row trigger, providing the constraint is not 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.
For example, dispxradores is an implementation of update cascade:. This implementation requires care for multirow updates. For example, if a table p has three rows with the values 123and table f also has three rows with the values 123then the following statement updates p correctly but causes problems when the trigger updates f:. The statement first updates 1 to 2 in p, and the trigger updates 1 to 2 in f, leaving two rows of value 2 in f.