5 Using Triggers

This chapter describes database triggers, which are stored procedural code that is associated with a database table, view, or event.

This chapter contains the following sections:

Designing Triggers

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:

TRIGGER trigger_name
  triggering_statement
  [trigger_restriction]
BEGIN
 triggered_action;
END;

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 (INSERT, UPDATE, and DELETE) on tables and views, DDL statements (CREATE, ALTER, and DROP) on schema objects, system errors, startup and shutdown of the database, and miscellaneous system actions. Triggering statements are subject to trigger restrictions.

  • A trigger restriction is the limitation that is placed on the trigger. This means that the database performs the triggered action only if the restriction evaluates to TRUE.

  • A triggered action is the body of the trigger, or the sequence of steps that are executed when both the appropriate statement fires the trigger and the restriction (if any) evaluates to TRUE.

See Also:

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 and Using Triggers

This sections shows how to create and use various types of triggers.

This section has the following topics:

See Also:

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.

Example 5-1 Creating a Log Table for the EVALUATIONS Table

The table evaluations_log stores entries with each INSERT, UPDATE or DELETE on the evaluations 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.

Example 5-2 Logging Operations with a Statement Trigger and Predicates

The trigger 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 INSERTING, UPDATING, or 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;

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.

Example 5-3 Generating Primary Keys FOR EACH ROW Triggers; BEFORE Option

  1. In the Connections navigation hierarchy, right-click Triggers.

  2. From the drop-down, select New Trigger.

    Description of create_trigger_1.gif follows
    Description of the illustration create_trigger_1.gif

  3. In the Create Trigger window, set the following parameters:

    • Set Name to new_evaluation_trigger.

    In the Trigger pane: set Trigger Type to TABLE, set Table Owner to HR, set Table Name to evaluations, select Before, select Insert, and select Row Level.

    Click OK.

    Description of create_trigger_2.gif follows
    Description of the illustration create_trigger_2.gif

  4. The 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.

    CREATE OR REPLACE
    TRIGGER new_evaluation
      BEFORE INSERT ON evaluations
      FOR EACH ROW
    BEGIN
      NULL;
    END;
    
  5. 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.

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.

Example 5-4 Updating Values from a View with an INSTEAD OF Trigger

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.

Example 5-5 Creating an access log table, hr_users_log

This table is the log of all logon and logoff events in the hr schema.

CREATE TABLE hr_users_log (user_name VARCHAR2(30), activity VARCHAR2(20),
                           event_date DATE);

Example 5-6 Creating a LOGON trigger

This trigger inserts a LOGON event record into the hr_users_log table whenever someone connects to the hr schema. Note that this is an AFTER trigger.

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

Example 5-7 Creating a LOGOFF trigger

This trigger inserts a LOGOFF event record into the hr_users_log table whenever someone disconnects from the hr schema. Note that this is a BEFORE trigger.

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.

Example 5-8 Modifying a Trigger

Replace the new_evaluation_trigger with the following code. New code is in bold font.

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.

Example 5-9 Disabling a Trigger

ALTER TRIGGER eval_change_trigger DISABLE;

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

Example 5-10 Enabling 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.

Example 5-11 Disabling All Triggers on a Table

ALTER TABLE evaluations DISABLE ALL TRIGGERS;

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

Example 5-12 Enable All Triggers on a Table

ALTER TABLE evaluations ENABLE ALL TRIGGERS;

See Also:

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 determine which trigger errors exist in the database.

Example 5-13 Displaying Trigger Compilation Errors

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.

Example 5-14 Displaying Trigger Dependencies

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.

Example 5-15 Displaying Trigger Compilation Errors

ALTER TRIGGER update_name_view_trigger COMPILE;

See Also:

Dropping Triggers

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

Example 5-16 Dropping a Trigger

DROP TRIGGER eval_change_trigger;

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

See Also: