|Oracle® Database 2 Day Developer's Guide
11g Release 1 (11.1)
|PDF · Mobi · ePub|
This chapter contains the following sections:
Triggers are stored procedural code that is fired automatically when specified events happen in the database. Triggers are associated with tables, views, or events. Unlike procedures and functions, triggers cannot be invoked directly. Instead, Oracle Database implicitly fires triggers when a triggering event occurs, regardless of the user or application. You may never be aware that a trigger is operating unless its operation causes an error that is not handled properly, when the event that fired the trigger fails.
The correct use of triggers enables you to build and deploy applications that are more robust, secure, and that use the database more effectively. These gains are possible because triggers can deliver the following features:
Data integrity checking and enforcement
Auditing and logging
Complex business logic modeling
Transaction validity checking and enforcement
Derived column generation
Table modification enabling and restriction
You can use triggers to enforce low-level business rules that are inherent to the database, and are therefore common for all client applications. For example, you may have several client applications that access the
employees table in the
hr schema. If a trigger on that table ensures the proper format of all data added to the table, this business logic does not have to be reproduced and maintained in every client application. Because the trigger cannot be circumvented by the client application, the business logic stored in the trigger is used automatically.
Each trigger has the following general form:
A trigger has four main parts:
A trigger name, which 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 (tables, views, and procedures); however, Oracle recommends that you adopt a consistent naming convention to avoid confusion.
A triggering statement is the event that initiates the firing of the trigger. These events include DML statements (
DELETE) on tables and views, DDL statements (
DROP) on schema objects, system errors, startup and shutdown of the database, and miscellaneous system actions. Triggering statements are subject to trigger restrictions.
Oracle Database PL/SQL Language Reference for general information about triggers
There are five different types of Oracle Database triggers.
You can have several different triggers associated with a specific DML statement; starting with Oracle Database Release 11g R1, you can specify the order in which they are executed by using the
PRECEDES clauses of the
CREATE TRIGGER statement.
Row triggers work in the same manner as statement triggers, but with two additional specifications. Row triggers use a
FOR EACH ROW clause in the triggering statement. They also allow you to reference the values of the rows, and event set them in the body of the trigger. This is particularly useful for inserting default values, or for overriding invalid values.
INSTEAD OF triggers on views run instead of the issuing statement. If an
INSERT statement is used on a view, an
INSTEAD OF trigger enables you to exercise fine control of what actually happens: insertion of data into the base table or another table, logging an insertion request without inserting data, and so on.
Also, Oracle Database may not be able to process an insert issued against a view, as in the case of derived columns; you can create a trigger that determines the values correctly. For example, if view used a column definition
last_name || ', ' || first_name, then you may write an
INSTEAD OF trigger that updates the characters before the comma character into the
last_name column, and the characters after the comma character into the
User event triggers may be used on DDL statements such as
DROP, on user
LOGOFF, and on specific DML actions (analysis and statistics, auditing, granting and revoking privilege, and so on).
LOGON triggers, which fire when a user connects to the database, are commonly used to set the environment for the user, and to execute functions that are associated with secure application roles.
Triggers can use
AFTER clauses in the triggering statement.
AFTER specify that the trigger should execute either before or after the event that fires the trigger. For statement and row triggers, a
BEFORE trigger can enhance security and enable business rules prior to making changes to the database, while the
AFTER trigger is ideal for logging actions.
You should consider the following guidelines and restrictions when planning triggers for your application:
Although triggers are useful for customizing a database, use them only when necessary. Excessive use of triggers can result in complex interdependencies, which can be difficult to maintain in a large application.
Ensure that when an action is performed, all related and dependent actions are performed.
Ensure that the triggers you create apply to the database and the business logic that is correct for the entire enterprise, regardless of specific users or client applications. If special rules apply only to some users and client applications and not to others, encapsulate that business logic within the application.
You cannot use
SAVEPOINT inside a trigger. Because DDL statements have an implicit
COMMIT, they are also not allowed in triggers, with the exception of
DROP TABLE, and
ALTER...COMPILE for system triggers.
This sections shows how to create and use various types of triggers.
This section has the following topics:
Oracle Database SQL Language Reference for information about creating triggers
Example 5-1 shows how to create a log table.
Example 5-2, you will create a trigger that writes to the
evaluations_log every time the
evaluations table changes.
eval_change_trigger tracks all changes made to the
evaluations table, and tracks them in the
evaluations_log table by adding to it a new row
AFTER these changes are made. Note that in this example, the body of the trigger uses a conditional predicate
DELETING, to determine which of the three possible statements fired the trigger.
CREATE OR REPLACE TRIGGER eval_modification_trigger AFTER INSERT OR UPDATE OR DELETE ON evaluations DECLARE log_action evaluations_log.action%TYPE; BEGIN IF INSERTING THEN log_action := 'Insert'; ELSIF UPDATING THEN log_action := 'Update'; ELSIF DELETING THEN log_action := 'Delete'; ELSE DBMS_OUTPUT.PUT_LINE('This code is not reachable.'); END IF; INSERT INTO evaluations_log (log_date, action) VALUES (SYSDATE, log_action); END;
Row triggers are executed for each affected row.
In "Using Sequences", you created the
evaluations_seq sequence as a primary key number generator for the
evaluations table. Oracle Database does not populate the primary key automatically, as part of the
CREATE TABLE statement. Instead, you must design a trigger that generates the unique number for the primary key with every
In the following task, you will use the SQL Developer Connection navigation hierarchy to create a trigger
new_evaluation, which checks if a new row should be added to the
evaluations table, based on whether a row for the same employee exists for the identical time period.
In the Connections navigation hierarchy, right-click Triggers.
From the drop-down, select New Trigger.
In the Create Trigger window, set the following parameters:
Set Name to
new_evaluation pane opens with the following code.
Note that the tile of the pane is in italic font, which indicates that the trigger is not saved in the database.
From the File menu, select Save to save the new trigger. Alternatively, use the CTRL + S key combination.
Note that Oracle Database automatically compiles triggers prior to saving them.
INSTEAD OF triggers enable you to implement changes to the underlying tables of a view. Such a trigger may be used on the
emp_locations view that you created in "Creating a View". Remember the definition of
CREATE VIEW emp_locations AS SELECT e.employee_id, e.last_name || ', ' || e.first_name name, d.department_name department, l.city city, c.country_name country FROM employees e, departments d, locations l, countries c WHERE e.department_id = d.department_id AND d.location_id = l.location_id AND l.country_id = c.country_id ORDER BY last_name;
Example 5-4 implements an
INSTEAD OF trigger
update_name_view_trigger to update the name of the employee.
CREATE OR REPLACE TRIGGER update_name_view_trigger INSTEAD OF UPDATE ON emp_locations BEGIN -- allow only the following update(s) UPDATE employees SET first_name = substr( :NEW.name, instr( :new.name, ',' )+2), last_name = substr( :NEW.name, 1, instr( :new.name, ',')-1) WHERE employee_id = :OLD.employee_id; END;
In Example 5-5, you will create a table
hr_users_log for keeping track of
LOGOFF events. You will then create triggers
note_hr_logon_trigger (in Example 5-6) and
note_hr_logoff_trigger (in Example 5-7) for writing these events to the log table.
CREATE TABLE hr_users_log (user_name VARCHAR2(30), activity VARCHAR2(20), event_date DATE);
new_evaluation_trigger has an empty body.
Example 5-8 demonstrates how to modify the trigger to assign to the
evaluation_id the next available value from the
On occasion, you may need to temporarily disable a trigger if an object it references is unavailable, or if you need to perform a large data upload (such as in recovery operations) without the delay that triggers cause.
Example 5-9 shows how to temporarily disable a trigger.
Example 5-10 shows how to re-enable a trigger.
When you need to disable all triggers on a particular table, you must use the statement
ALTER TABLE ... DISABLE ALL TRIGGERS. To re-enable all the triggers for the table, use the statement
ALTER TABLE ... ENABLE ALL TRIGGERS.
Example 5-11 shows how to temporarily disable all triggers that are defined on a particular table.
Example 5-12 shows how to re-enable all triggers that are defined on a particular table.
A trigger is fully compiled when the
CREATE TRIGGER statement is executed. If a trigger compilation produces an error, the DML statement fails. To see the relevant compilation errors, use the
Example 5-13 shows how to determine which trigger errors exist in the database.
Once a trigger is compiled, it creates dependencies on the underlying database objects, and becomes invalid if these objects are either removed or modified so that there is a mismatch between the trigger and the object. The invalidated triggers are recompiled during their next invocation.
Example 5-14 shows how to determine the dependencies triggers have on other objects in the database.
To re-compile a trigger manually, you must use the
ALTER TRIGGER ... COMPILE statement, as shown in Example 5-15.
Oracle Database PL/SQL Language Reference for details about compiling triggers
When you need to delete a trigger, use the
DROP TRIGGER statement, as shown in Example 5-16.
After you drop a trigger, you can drop the dependent object that are no longer needed by the application.
Oracle Database SQL Language Reference for information about the
DROP TRIGGER statement