Using Triggers in Oracle
You use constraints to automatically enforce data integrity rules whenever a user tries to write or modify a row in a table. There are times when you want to use the same kind of timing for your own application-specific logic. Oracle includes triggers to give you this capability.
Although you can write triggers to perform the work of a constraint, Oracle has optimized the operation of constraints, so it is best to
always use a constraint instead of a trigger if possible.
A trigger is a block of code that is fired whenever a particular type of database event occurs to a table. There are three types of events that can cause a trigger to fire:
- A database UPDATE
- A database INSERT
- A database DELETE
You can, for instance, define a trigger to write a customized audit record whenever a user changes a row.
Triggers are defined at the row level. Youcan specify that a trigger be fired for each row or for the SQL statement that fires the trigger event. As with the previous discussion of constraints, a single SQL
statement can affect many rows, so the specification of the trigger can have a significant effect on the operation of the trigger and the performance of the database.
There are three times when a trigger can fire:
- Before the execution of the triggering event
- After the execution of the triggering event
- Instead of the triggering event
Combining the first two timing options with the row and statement versions of a trigger gives youfou r possible trigger implementations: before a statement, before a row, after a statement, and after a row.
Oracle Database 11g introduced the concept of compound triggers. With this enhancement, a single trigger can have a section for the different timing implementations. Compound triggers help to improve performance, since the trigger has to be loaded only once for multiple timing options. INSTEAD OF triggers were introduced with Oracle8.
The INSTEAD OF trigger has a specific purpose: to implement data-manipulation operations on views that do not normally permit them, such as a view that references columns in more than one base table for updates. You should be careful when using INSTEAD OF triggers because of the many potential problems associated with modifying the data in the underlying base tables of a view. There are many restrictions on when you can use INSTEAD OF triggers.
You can specify a trigger restriction for any trigger. A trigger restriction is a Boolean expression that circumvents the execution of the trigger if
it evaluates to FALSE. Triggers are defined and stored separately from the tables that use them. Since they contain logic, they must be written in a language with capabilities beyond those of SQL, which is designed to access data.
Oracle8 and later versions allow a developer to write triggers in PL/SQL, the procedural language that has been a part of Oracle since Version 6. Oracle8i and beyond also support Java as a procedural language, so you can create Java triggers with those versions.
You can write a trigger directly in PL/SQL or Java, or a trigger can call an existing stored procedure written in either language.
Triggers are fired as a result of a SQL statement that affects a row in a particular table. It is possible for the actions of the trigger to modify the data in the table or to cause changes in other tables that fire their own triggers. The end result of this may be data that ends up being changed in a way that Oracle thinks is logically illegal. These situations can cause Oracle to return runtime errors referring to mutating
tables, which are tables modified by other triggers, or constraining tables, which are tables modified by other constraints. Oracle8i eliminated some of the errors caused by activating constraints with triggers.
If you want to create an integrity check that goes beyond the boundaries of a
CHECK constraint you can define a trigger on the table.
A trigger is written in PL/SQL, in Oracle9i, or in Java, and can include virtually any logical operation.
You should not use a trigger if a constraint will serve the same purpose, since the Oracle database has optimized the way it enforces constraints.
You may also need to use a trigger to compensate for other limitations in other constraints.
For instance, a
FOREIGN KEY constraint will not work if the referenced table is on another node in a distributed database.
A trigger is a PL/SQL unit that is stored in the database and (if it is in the enabled state) automatically executes ("fires") in response to a specified event. A trigger has this structure:
[ trigger_restriction ]
The trigger_name must be unique for triggers in the schema and a trigger can have the same name as another kind of object in the schema (for example, a table). However, Oracle recommends using a naming convention that avoids confusion.
If the trigger is in the enabled state, the triggering_event causes the database to execute the triggered_action if the trigger_restriction is either TRUE or omitted.
The triggering_event is associated with either a table, a view, a schema, or the database, and it is one of these:
- DML statement
- DDL statement
- Database operation