5 Using Triggers

Types of Triggers

There are five different types of Oracle Database triggers.

• Statement triggers are associated with a DML statement, such as DELETE, INSERT, or UPDATE, on a specified table or view.

  Note that statement triggers fire once for each DML statement. For example, an UPDATE statement trigger will execute only once, regardless of the number of affected rows in the table.

  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 FOLLOWS and PRECEDES clauses of the CREATE TRIGGER statement.

• Row triggers are fired for each row that is affected by an INSERT, UPDATE, or DELETE statement on a table.

  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 first_name column.

• User event triggers may be used on DDL statements such as CREATE, ALTER, or DROP, on user LOGON and 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.

• System event triggers apply to database startup, database shutdown, or server error events. These events are not associated with specific tables, views, or rows.

Timing Triggers

Triggers can use BEFORE or AFTER clauses in the triggering statement. BEFORE and 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.

INSTEAD OF triggers do not use BEFORE or AFTER options. By default, they use the same semantics as AFTER row-level triggers.

System and user event triggers can use BEFORE and AFTER clauses, with obvious exceptions: only AFTER is valid for STARTUP, SUSPEND, and LOGON, and only BEFORE is valid for SHUTDOWN and LOGOFF.

Guidelines and Restrictions for Trigger Design

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.

• Avoid recursive triggers because they can quickly exhaust system memory.

• Be aware of cascading triggers, as they may have unintended effects and performance implications.

• Avoid triggers that duplicate existing Oracle Database offerings; for example, do not design triggers that reject bad data that can be eliminated through declarative integrity constraints.

• Ensure that you use the BEFORE and AFTER clauses correctly to efficiently implement business rules. A BEFORE EACH ROW trigger can change the :NEW values.

• Limit the size of triggers, as they cannot exceed 32Kb. If a trigger requires many lines of code, consider moving the business logic to a stored procedure that is invoked from the trigger.

• 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 COMMIT, ROLLBACK, or SAVEPOINT inside a trigger. Because DDL statements have an implicit COMMIT, they are also not allowed in triggers, with the exception of CREATE, ALTER, DROP TABLE, and ALTER...COMPILE for system triggers.

• Only committed system triggers are fired.

Creating a Statement Trigger

Statement triggers relate to a particular statement, such as INSERT, UPDATE, or DELETE. You can use a statement trigger for logging such operations as they are performed on a particular table.

Example 5-1 shows how to create a log table.

CREATE TABLE evaluations_log (log_date DATE 
                            , action VARCHAR2(50));

Example 5-2, you will create a trigger that writes to the evaluations_log every time the evaluations table changes.

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;

Creating a Row Trigger

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 INSERT statement.

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.

Creating an INSTEAD OF Trigger

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 emp_locations:

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;

Creating LOGON and LOGOFF Triggers

LOGON and LOGOFF triggers monitor who uses the database by writing to a log table.

In Example 5-5, you will create a table hr_users_log for keeping track of LOGON and 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);

CREATE OR REPLACE TRIGGER note_hr_logon_trigger 
  AFTER LOGON
  ON HR.SCHEMA
BEGIN
  INSERT INTO hr_users_log VALUES (USER, 'LOGON', SYSDATE);
END;

CREATE OR REPLACE TRIGGER note_hr_logoff_trigger 
  BEFORE LOGOFF
  ON HR.SCHEMA
BEGIN
  INSERT INTO hr_users_log VALUES (USER, 'LOGOFF', SYSDATE);
END;

Modifying Triggers

The 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 evaluations_seq sequence.

CREATE OR REPLACE TRIGGER new_evaluation_trigger
  BEFORE INSERT ON evaluations FOR EACH ROW
BEGIN
  :NEW.evaluation_id := evaluations_seq.NEXTVAL;
END;

Disabling and Enabling Triggers

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.

To disable a trigger, you must use the ALTER TRIGGER ... DISABLE statement. To re-enable the trigger, use the ALTER TRIGGER ... ENABLE statement.

Example 5-9 shows how to temporarily disable a trigger.

ALTER TRIGGER eval_change_trigger DISABLE;

Example 5-10 shows how to re-enable a trigger.

ALTER TRIGGER eval_change_trigger ENABLE;

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.

ALTER TABLE evaluations DISABLE ALL TRIGGERS;

Example 5-12 shows how to re-enable all triggers that are defined on a particular table.

ALTER TABLE evaluations ENABLE ALL TRIGGERS;

Compiling Triggers

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 USER_ERRORS view.

Example 5-13 shows how to

SELECT * FROM USER_ERRORS WHERE TYPE = 'TRIGGER';

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.

SELECT * FROM ALL_DEPENDENCIES WHERE TYPE = 'TRIGGER';

To re-compile a trigger manually, you must use the ALTER TRIGGER ... COMPILE statement, as shown in Example 5-15.

To re-compile a trigger manually, you must use the ALTER TRIGGER ... COMPILE statement, as shown in the following example.

ALTER TRIGGER update_name_view_trigger COMPILE;

Dropping Triggers

When you need to delete a trigger, use the DROP TRIGGER statement, as shown in Example 5-16.

DROP TRIGGER eval_change_trigger;

After you drop a trigger, you can drop the dependent object that are no longer needed by the application.