Examples of Trigger Applications

• provide sophisticated auditing

• prevent invalid transactions

• enforce referential integrity (either those actions not supported by declarative integrity constraints or across nodes in a distributed database)

• enforce complex business rules

• enforce complex security authorizations

• provide transparent event logging

• automatically generate derived column values

Auditing with Triggers

Sometimes, the Oracle AUDIT command is considered a security audit facility, while triggers can provide financial audit facility.

When deciding whether to create a trigger to audit database activity, consider what Oracle's auditing features provide, compared to auditing defined by triggers.

DML as well as DDL auditing	Standard auditing options permit auditing of DML and DDL statements regarding all types of schema objects and structures. Comparatively, triggers only permit auditing of DML statements issued against tables.
Centralized audit trail	All database audit information is recorded centrally and automatically using the auditing features of Oracle.
Declarative method	Auditing features enabled using the standard Oracle features are easier to declare and maintain, and less prone to errors when compared to auditing functions defined by triggers.
Auditing options can be audited	Any changes to existing auditing options can also be audited to guard against malicious database activity.
Session and execution time auditing	Using the database auditing features, records can be generated once every time an audited statement is issued (BY ACCESS) or once for every session that issues an audited statement (BY SESSION). Triggers cannot audit by session; an audit record is generated each time a trigger-audited table is referenced.
Auditing of unsuccessful data access	Database auditing can be set to audit when unsuccessful data access occurs. However, any audit information generated by a trigger is rolled back if the triggering statement is rolled back.
Sessions can be audited	Connections and disconnections, as well as session activity (physical I/Os, logical I/Os, deadlocks, etc.), can be recorded using standard database auditing.

When using triggers to provide sophisticated auditing, AFTER triggers are normally used. By using AFTER triggers, auditing information is recorded after the triggering statement is subjected to any applicable integrity constraints, preventing cases where the audit processing is carried out unnecessarily for statements that generate exceptions to integrity constraints.

When to use AFTER row vs. AFTER statement triggers depends on the information being audited. For example, row triggers provide value-based auditing on a per-row basis for tables. Triggers can also require the user to supply a "reason code" for issuing the audited SQL statement, which can be useful in both row and statement-level auditing situations.

The following example demonstrates a trigger that audits modifications to the EMP table on a per-row basis. It requires that a "reason code" be stored in a global package variable before the update.

Example

This trigger demonstrates

• how triggers can be used to provide value-based auditing

• how to use public package variables

CREATE TRIGGER audit_employee
AFTER INSERT OR DELETE OR UPDATE ON emp
FOR EACH ROW
BEGIN
/* AUDITPACKAGE is a package with a public package
   variable REASON.  REASON could be set by the
   application by a command such as EXECUTE
   AUDITPACKAGE.SET_REASON(reason_string). Note that a
   package variable has state for the duration of a
   session and that each session has a separate copy of
   all package variables. */

IF auditpackage.reason IS NULL THEN
   raise_application_error(-20201, 'Must specify reason'
      || ' with AUDITPACKAGE.SET_REASON(reason_string)');
END IF;

/* If the above conditional evaluates to TRUE, the
   user-specified error number and message is raised,
   the trigger stops execution, and the effects of the
   triggering statement are rolled back.  Otherwise, a
   new row is inserted into the predefined auditing
   table named AUDIT_EMPLOYEE containing the existing
   and new values of the EMP table and the reason code
   defined by the REASON variable of AUDITPACKAGE.  Note
   that the "old" values are NULL if triggering
   statement is an INSERT and the "new" values are NULL
   if the triggering statement is a DELETE. */


INSERT INTO audit_employee VALUES
   (:old.ssn, :old.name, :old.job_classification, :old.sal,
   :new.ssn, :new.name, :new.job_classification, :new.sal,
   auditpackage.reason, user, sysdate );
END;

Optionally, you can also set the reason code back to NULL if you wanted to force the reason code to be set for every update. The following simple AFTER statement trigger sets the reason code back to NULL after the triggering statement is executed:

CREATE TRIGGER audit_employee_reset
AFTER INSERT OR DELETE OR UPDATE ON emp
BEGIN
   auditpackage.set_reason(NULL);
END;

Notice that the previous two triggers are both fired by the same type of SQL statement. However, the AFTER row trigger is fired once for each row of the table affected by the triggering statement, while the AFTER statement trigger is fired only once after the triggering statement execution is completed.

Another example of using triggers to do auditing is shown below. This trigger tracks changes being made to the EMP table, and stores this information in AUDIT_TABLE and AUDIT_TABLE_VALUES.

CREATE OR REPLACE TRIGGER audit_emp
   AFTER INSERT OR UPDATE OR DELETE ON emp
   FOR EACH ROW
   DECLARE
      time_now DATE;
      terminal CHAR(10);
   BEGIN

      -- get current time, and the terminal of the user
      time_now := SYSDATE;
      terminal := USERENV('TERMINAL');

      -- record new employee primary key
      IF INSERTING THEN 
         INSERT INTO audit_table
            VALUES (audit_seq.NEXTVAL, user, time_now,
               terminal, 'EMP', 'INSERT', :new.empno);

      -- record primary key of the deleted row
      ELSIF DELETING THEN                           
         INSERT INTO audit_table
            VALUES (audit_seq.NEXTVAL, user, time_now,
               terminal, 'EMP', 'DELETE', :old.empno);

      -- for updates, record the primary key
      -- of the row being updated
      ELSE 
         INSERT INTO audit_table
            VALUES (audit_seq.NEXTVAL, user, time_now,
               terminal, 'EMP', 'UPDATE', :old.empno);

         -- and for SAL and DEPTNO, record old and new values
         IF UPDATING ('SAL') THEN
            INSERT INTO audit_table_values
               VALUES (audit_seq.CURRVAL, 'SAL',
                  :old.sal, :new.sal);



         ELSIF UPDATING ('DEPTNO') THEN
            INSERT INTO audit_table_values
               VALUES (audit_seq.CURRVAL, 'DEPTNO',
                  :old.deptno, :new.deptno);
         END IF;
      END IF;
 END;
/

Integrity Constraints and Triggers

Declarative integrity constraints are statements about the database that are always true. A constraint applies to existing data in the table and any statement that manipulates the table; for more information, see Chapter 6.

Triggers constrain what a transaction can do. A trigger does not apply to data loaded before the definition of the trigger; therefore, it is not known if all data in a table conforms to the rules established by an associated trigger.

Although triggers can be written to enforce many of the same rules supported by Oracle's declarative integrity constraint features, triggers should only be used to enforce complex business rules that cannot be defined using standard integrity constraints. The declarative integrity constraint features provided with Oracle offer the following advantages when compared to constraints defined by triggers:

Centralized integrity checks	All points of data access must adhere to the global set of rules defined by the integrity constraints corresponding to each schema object.
Declarative method	Constraints defined using the standard integrity constraint features are much easier to write and are less prone to errors when compared with comparable constraints defined by triggers.

While most aspects of data integrity can be defined and enforced using declarative integrity constraints, triggers can be used to enforce complex business constraints not definable using declarative integrity constraints. For example, triggers can be used to enforce

• UPDATE and DELETE SET NULL, and UPDATE and DELETE SET DEFAULT referential actions

• referential integrity when the parent and child tables are on different nodes of a distributed database

• complex check constraints not definable using the expressions allowed in a CHECK constraint

Enforcing Referential Integrity Using Triggers

When using triggers to maintain referential integrity, declare the PRIMARY (or UNIQUE) KEY constraint in the parent table. If referential integrity is being maintained between a parent and child table in the same database, you can also declare the foreign key in the child table, but disable it; this prevents the corresponding PRIMARY KEY constraint from being dropped (unless the PRIMARY KEY constraint is explicitly dropped with the CASCADE option).

To maintain referential integrity using triggers:

• A trigger must be defined for the child table that guarantees values inserted or updated in the foreign key correspond to values in the parent key.

• One or more triggers must be defined for the parent table. These triggers guarantee the desired referential action (RESTRICT, CASCADE, or SET NULL) for values in the foreign key when values are updated or deleted in the parent key. No action is required for inserts into the parent table (no dependent foreign keys exist).

Several of the triggers include statements that lock rows (SELECT ... FOR UPDATE). This operation is necessary to maintain concurrency as the rows are being processed.

Foreign Key Trigger for Child Table The following trigger guarantees that before an INSERT or UPDATE statement affects a foreign key value, the corresponding value exists in the parent key. The mutating table exception included in the example below allows this trigger to be used with the UPDATE_SET_DEFAULT and UPDATE_CASCADE triggers. This exception can be removed if this trigger is used alone.

CREATE TRIGGER emp_dept_check
BEFORE INSERT OR UPDATE OF deptno ON emp
FOR EACH ROW WHEN (new.deptno IS NOT NULL)

-- Before a row is inserted, or DEPTNO is updated in the EMP
-- table, fire this trigger to verify that the new foreign
-- key value (DEPTNO) is present in the DEPT table.
DECLARE
   dummy INTEGER;  -- used for cursor fetch below
   invalid_department EXCEPTION;
   valid_department EXCEPTION;
   mutating_table EXCEPTION;
   PRAGMA EXCEPTION_INIT (mutating_table, -4091);
-- Cursor used to verify parent key value exists.  If
-- present, lock parent key's row so it can't be
-- deleted by another transaction until this
-- transaction is committed or rolled back.
CURSOR PRINT_SALARY_CHANGES_cursor (dn NUMBER) IS
   SELECT deptno
   FROM dept
   WHERE deptno = dn
   FOR UPDATE OF deptno;
BEGIN
   OPEN dummy_cursor (:new.deptno);
   FETCH dummy_cursor INTO dummy;

   -- Verify parent key.  If not found, raise user-specified
   -- error number and message.  If found, close cursor
   -- before allowing triggering statement to complete.
   IF dummy_cursor%NOTFOUND THEN
      RAISE invalid_department;
   
ELSE
      RAISE valid_department;
   END IF;
   CLOSE dummy_cursor;
EXCEPTION
   WHEN invalid_department THEN
      CLOSE dummy_cursor;
      raise_application_error(-20000, 'Invalid Department'
         || ' Number' || TO_CHAR(:new.deptno));
   WHEN valid_department THEN
      CLOSE dummy_cursor;
   WHEN mutating_table THEN
      NULL;
END;

UPDATE and DELETE RESTRICT Trigger for the Parent Table The following trigger is defined on the DEPT table to enforce the UPDATE and DELETE RESTRICT referential action on the primary key of the DEPT table:

CREATE TRIGGER dept_restrict
BEFORE DELETE OR UPDATE OF deptno ON dept
FOR EACH ROW

-- Before a row is deleted from DEPT or the primary key
-- (DEPTNO) of DEPT is updated, check for dependent
-- foreign key values in EMP; rollback if any are found.
DECLARE
   dummy INTEGER;      -- used for cursor fetch below
   employees_present EXCEPTION;
   employees_not_present EXCEPTION;

   -- Cursor used to check for dependent foreign key values.
   CURSOR dummy_cursor (dn NUMBER) IS
      SELECT deptno FROM emp WHERE deptno = dn;





BEGIN
   OPEN dummy_cursor (:old.deptno);
   FETCH dummy_cursor INTO dummy;

   -- If dependent foreign key is found, raise user-specified
   -- error number and message.  If not found, close cursor
   -- before allowing triggering statement to complete.
   IF dummy_cursor%FOUND THEN
      RAISE employees_present; /* dependent rows exist */
   ELSE
      RAISE employees_not_present; /* no dependent rows */
   END IF;
   CLOSE dummy_cursor;

EXCEPTION
   WHEN employees_present THEN
      CLOSE dummy_cursor;
      raise_application_error(-20001, 'Employees Present in'
         || ' Department ' || TO_CHAR(:old.deptno));
   WHEN employees_not_present THEN
      CLOSE dummy_cursor;
END;

Note: This trigger will not work with self-referential tables (that is, tables with both the primary/unique key and the foreign key). Also, this trigger does not allow triggers to cycle (such as, A fires B fires A).

UPDATE and DELETE SET NULL Triggers for Parent Table The following trigger is defined on the DEPT table to enforce the UPDATE and DELETE SET NULL referential action on the primary key of the DEPT table:

CREATE TRIGGER dept_set_null
AFTER DELETE OR UPDATE OF deptno ON dept
FOR EACH ROW

-- Before a row is deleted from DEPT or the primary key
-- (DEPTNO) of DEPT is updated, set all corresponding
-- dependent foreign key values in EMP to NULL.
BEGIN
   IF UPDATING AND :OLD.deptno != :NEW.deptno OR DELETING THEN
      UPDATE emp SET emp.deptno = NULL
         WHERE emp.deptno = :old.deptno;
   END IF;
END;

DELETE Cascade Trigger for Parent Table The following trigger on the DEPT table enforces the DELETE CASCADE referential action on the primary key of the DEPT table:

CREATE TRIGGER dept_del_cascade
AFTER DELETE ON dept
FOR EACH ROW

-- Before a row is deleted from DEPT, delete all
-- rows from the EMP table whose DEPTNO is the same as
-- the DEPTNO being deleted from the DEPT table.
BEGIN
   DELETE FROM emp
      WHERE emp.deptno = :old.deptno;
END;

Note: Typically, the code for DELETE cascade is combined with the code for UPDATE SET NULL or UPDATE SET DEFAULT to account for both updates and deletes.

UPDATE Cascade Trigger for Parent Table The following trigger ensures that if a department number is updated in the DEPT table, this change is propagated to dependent foreign keys in the EMP table:

-- Generate a sequence number to be used as a flag for
-- determining if an update has occurred on a column.
CREATE SEQUENCE update_sequence
    INCREMENT BY 1 MAXVALUE 5000
    CYCLE;

CREATE PACKAGE integritypackage AS
   updateseq NUMBER;
END integritypackage;

CREATE or replace PACKAGE BODY integritypackage AS
END integritypackage;
ALTER TABLE emp ADD update_id NUMBER;   -- create flag col.

CREATE TRIGGER dept_cascade1 BEFORE UPDATE OF deptno ON dept
DECLARE
   dummy NUMBER;

-- Before updating the DEPT table (this is a statement
-- trigger), generate a new sequence number and assign
-- it to the public variable UPDATESEQ of a user-defined
-- package named INTEGRITYPACKAGE.
BEGIN
   SELECT update_sequence.NEXTVAL
      INTO dummy
      FROM dual;
   integritypackage.updateseq := dummy;
END;

CREATE TRIGGER dept_cascade2 AFTER DELETE OR UPDATE
   OF deptno ON dept FOR EACH ROW

-- For each department number in DEPT that is updated,
-- cascade the update to dependent foreign keys in the
-- EMP table.  Only cascade the update if the child row
-- has not already been updated by this trigger.
BEGIN
   IF UPDATING THEN
      UPDATE emp
         SET deptno = :new.deptno,
         update_id = integritypackage.updateseq /*from 1st*/
         WHERE emp.deptno = :old.deptno
         AND update_id IS NULL;
         /* only NULL if not updated by the 3rd trigger
            fired by this same triggering statement */
   END IF;
   IF DELETING THEN

   -- Before a row is deleted from DEPT, delete all
   -- rows from the EMP table whose DEPTNO is the same as
   -- the DEPTNO being deleted from the DEPT table.
      DELETE FROM emp
      WHERE emp.deptno = :old.deptno;
   END IF;
END;

CREATE TRIGGER dept_cascade3 AFTER UPDATE OF deptno ON dept
BEGIN  UPDATE emp
   SET update_id = NULL
   WHERE update_id = integritypackage.updateseq;
END;

Note: Because this trigger updates the EMP table, the EMP_DEPT_CHECK trigger, if enabled, is also fired. The resulting mutating table error is trapped by the EMP_DEPT_CHECK trigger. You should carefully test any triggers that require error trapping to succeed to ensure that they will always work properly in your environment.

Enforcing Complex Check Constraints

CREATE TRIGGER salary_check
BEFORE INSERT OR UPDATE OF sal, job ON emp
FOR EACH ROW
DECLARE
   minsal                NUMBER;
   maxsal                NUMBER;
   salary_out_of_range   EXCEPTION;
BEGIN

/* Retrieve the minimum and maximum salary for the
   employee's new job classification from the SALGRADE
   table into MINSAL and MAXSAL. */

SELECT minsal, maxsal INTO minsal, maxsal FROM salgrade
   WHERE job_classification = :new.job;


/* If the employee's new salary is less than or greater
   than the job classification's limits, the exception is
   raised.  The exception message is returned and the
   pending INSERT or UPDATE statement that fired the
   trigger is rolled back. */

   IF (:new.sal < minsal OR :new.sal > maxsal) THEN
      RAISE salary_out_of_range;
   END IF;
EXCEPTION
   WHEN salary_out_of_range THEN
      raise_application_error (-20300,
         'Salary '||TO_CHAR(:new.sal)||' out of range for '
         ||'job classification '||:new.job
         ||' for employee '||:new.name);
   WHEN NO_DATA_FOUND THEN
      raise_application_error(-20322,
         'Invalid Job Classification '
         ||:new.job_classification);
END;

Complex Security Authorizations and Triggers

When using a trigger to enforce a complex security authorization, it is best to use a BEFORE statement trigger. Using a BEFORE statement trigger has these benefits:

• The security check is done before the triggering statement is allowed to execute so that no wasted work is done by an unauthorized statement.

• The security check is performed only once for the triggering statement, not for each row affected by the triggering statement.

This example shows a trigger used to enforce security. The comments within the code explain the functionality of the trigger.

CREATE TRIGGER emp_permit_changes
BEFORE INSERT OR DELETE OR UPDATE ON emp
DECLARE
   dummy INTEGER;
   not_on_weekends EXCEPTION;
   not_on_holidays EXCEPTION;
   non_working_hours EXCEPTION;
BEGIN
   /* check for weekends */
   IF (TO_CHAR(sysdate, 'DY') = 'SAT' OR
      TO_CHAR(sysdate, 'DY') = 'SUN') THEN
      RAISE not_on_weekends;
   END IF;
   /* check for company holidays */
   SELECT COUNT(*) INTO dummy FROM company_holidays
      WHERE TRUNC(day) = TRUNC(sysdate);
      /* TRUNC gets rid of time parts of dates */
   IF dummy > 0 THEN
      RAISE not_on_holidays;
   END IF;
   /* Check for work hours (8am to 6pm) */
   IF (TO_CHAR(sysdate, 'HH24') < 8 OR
      TO_CHAR(sysdate, 'HH24') > 18) THEN
      RAISE non_working_hours;
   END IF;
EXCEPTION
   WHEN not_on_weekends THEN
      raise_application_error(-20324,'May not change '
         ||'employee table during the weekend');
   WHEN not_on_holidays THEN
      raise_application_error(-20325,'May not change '
         ||'employee table during a holiday');
   WHEN non_working_hours THEN
      raise_application_error(-20326,'May not change '
      ||'emp table during non-working hours');
END;

Transparent Event Logging and Triggers

Example

The REORDER trigger example shows a trigger that reorders parts as necessary when certain conditions are met (that is, a triggering statement is issued and the PARTS_ON_HAND value is less than the REORDER_POINT value).

Derived Column Values and Triggers

• the dependant values must be derived before the insert or update occurs so that the triggering statement can use the derived values.

• the trigger must fire for each row affected by the triggering INSERT or UPDATE statement.

The following example illustrates how a trigger can be used to derive new column values for a table whenever a row is inserted or updated. Comments within the code explain its functionality.

BEFORE INSERT OR UPDATE OF ename ON emp

/* Before updating the ENAME field, derive the values for
   the UPPERNAME and SOUNDEXNAME fields. Users should be
   restricted from updating these fields directly. */
FOR EACH ROW

BEGIN
   :new.uppername := UPPER(:new.ename);
   :new.soundexname := SOUNDEX(:new.ename);
END;