You can use triggers in a number of ways to customize information management in the database. For example, triggers are commonly used to:
This section provides an example of each of these trigger applications. These examples are not meant to be used exactly as written: They are provided to assist you in designing your own triggers.
Triggers are commonly used to supplement the built-in auditing features of the database. Although triggers can be written to record information similar to that recorded by the AUDIT statement, use triggers only when more detailed audit information is required. For example, use triggers to provide value-based auditing for each row.
Sometimes, the AUDIT statement 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 the database's auditing features provide, compared to auditing defined by triggers, as shown in Table 9-2.
Table 9-2 Comparison of Built-in Auditing and Trigger-Based Auditing
| Audit Feature | Description |
|---|---|
|
DML and DDL Auditing |
Standard auditing options permit auditing of DML and DDL statements regarding all types of schema objects and structures. Comparatively, triggers permit auditing of DML statements entered against tables, and DDL auditing at SCHEMA or DATABASElevel. |
|
Centralized Audit Trail |
All database audit information is recorded centrally and automatically using the auditing features of the database. |
|
Declarative Method |
Auditing features enabled using the standard database 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 entered (BY ACCESS) or once for every session that enters 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, unless autonomous transactions are used, any audit information generated by a trigger is rolled back if the triggering statement is rolled back. For more information about autonomous transactions, see Oracle Database Concepts. |
|
Sessions can be Audited |
Connections and disconnections, as well as session activity (physical I/Os, logical I/Os, deadlocks, and so on), can be recorded using standard database auditing. |
When using triggers to provide sophisticated auditing, AFTER triggers are normally used. The triggering statement is subjected to any applicable constraints. If no records are found, then the AFTER trigger does not fire, and audit processing is not carried out unnecessarily.
Choosing between AFTER row and AFTER statement triggers depends on the information being audited. For example, row triggers provide value-based auditing for each table row. 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 for each row. It requires that a "reason code" be stored in a global package variable before the update. This shows how triggers can be used to provide value-based auditing and how to use public package variables.
Note:
You might need to set up the following data structures for the examples to work:
CREATE OR REPLACE PACKAGE Auditpackage AS Reason VARCHAR2(10); PROCEDURE Set_reason(Reason VARCHAR2); END; CREATE TABLE Emp99 ( Empno NOT NULL NUMBER(4), Ename VARCHAR2(10), Job VARCHAR2(9), Mgr NUMBER(4), Hiredate DATE, Sal NUMBER(7,2), Comm NUMBER(7,2), Deptno NUMBER(2), Bonus NUMBER, Ssn NUMBER, Job_classification NUMBER); CREATE TABLE Audit_employee ( Oldssn NUMBER, Oldname VARCHAR2(10), Oldjob VARCHAR2(2), Oldsal NUMBER, Newssn NUMBER, Newname VARCHAR2(10), Newjob VARCHAR2(2), Newsal NUMBER, Reason VARCHAR2(10), User1 VARCHAR2(10), Systemdate DATE);
CREATE OR REPLACE TRIGGER Audit_employee
AFTER INSERT OR DELETE OR UPDATE ON Emp99
FOR EACH ROW
BEGIN
/* AUDITPACKAGE is a package with a public package
variable REASON. REASON can be set by the
application by a statement such as EXECUTE
AUDITPACKAGE.SET_REASON(reason_string).
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 preceding condition evaluates to TRUE,
user-specified error number & message is raised,
trigger stops execution, & effects of triggering statement are rolled back.
Otherwise, new row is inserted
into predefined auditing table named AUDIT_EMPLOYEE
containing existing & new values of the emp table
& reason code defined by REASON variable of AUDITPACKAGE.
"Old" values are NULL if triggering statement is INSERT
& "new" values are NULL if triggering statement is DELETE. */
INSERT INTO Audit_employee VALUES (
:OLD.Ssn, :OLD.Ename, :OLD.Job_classification, :OLD.Sal,
:NEW.Ssn, :NEW.Ename, :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 run:
CREATE OR REPLACE 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 fired by the same type of SQL statement. However, the AFTER row trigger fires once for each row of the table affected by the triggering statement, while the AFTER statement trigger fires only once after the triggering statement execution is completed.
This next trigger also uses triggers to do auditing. It tracks changes made to the emp table and stores this information in audit_table and audit_table_values.
Note:
You might need to set up the following data structures for the example to work:
CREATE TABLE audit_table ( Seq NUMBER, User_at VARCHAR2(10), Time_now DATE, Term VARCHAR2(10), Job VARCHAR2(10), Proc VARCHAR2(10), enum NUMBER); CREATE SEQUENCE audit_seq; CREATE TABLE audit_table_values ( Seq NUMBER, Dept NUMBER, Dept1 NUMBER, Dept2 NUMBER);
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, & terminal of 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 deleted row:
ELSIF DELETING THEN
INSERT INTO audit_table VALUES (
Audit_seq.NEXTVAL, User, Time_now,
Terminal, 'emp', 'DELETE', :OLD.Empno
);
-- For updates, record primary key of row being updated:
ELSE
INSERT INTO audit_table VALUES (
audit_seq.NEXTVAL, User, Time_now,
Terminal, 'emp', 'UPDATE', :OLD.Empno
);
-- For SAL & DEPTNO, record old & 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;
Triggers and declarative constraints can both be used to constrain data input. However, triggers and constraints have significant differences.
Declarative 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.
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 declarative constraint features, use triggers only to enforce complex business rules that cannot be defined using standard constraints. The declarative constraint features provided with the database offer the following advantages when compared to constraints defined by triggers:
All points of data access must adhere to the global set of rules defined by the constraints corresponding to each schema object.
Constraints defined using the standard 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 constraints, triggers can be used to enforce complex business constraints not definable using declarative constraints. For example, triggers can be used to enforce:
Use triggers only when performing an action for which there is no declarative support.
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, then you can also declare the foreign key in the child table, but disable it. Disabling the trigger in the child table 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:
The following topics provide examples of the triggers necessary to enforce referential integrity:
The examples in the following sections use the emp and dept table relationship. 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.
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 following example 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 OR REPLACE TRIGGER Emp_dept_check
BEFORE INSERT OR UPDATE OF Deptno ON emp
FOR EACH ROW WHEN (new.Deptno IS NOT NULL)
-- Before row is inserted or DEPTNO is updated in emp table,
-- fire this trigger to verify that new foreign key value (DEPTNO)
-- is present in dept table.
DECLARE
Dummy INTEGER; -- Use for cursor fetch
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 cannot be deleted
-- by another transaction until this transaction is
-- committed or rolled back.
CURSOR Dummy_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 & 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;
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 OR REPLACE TRIGGER Dept_restrict
BEFORE DELETE OR UPDATE OF Deptno ON dept
FOR EACH ROW
-- Before row is deleted from dept or primary key (DEPTNO) of dept is updated,
-- check for dependent foreign key values in emp;
-- if any are found, roll back.
DECLARE
Dummy INTEGER; -- Use for cursor fetch
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 exist
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;
Caution:
This trigger does not work with self-referential tables (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).
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 OR REPLACE TRIGGER Dept_set_null
AFTER DELETE OR UPDATE OF Deptno ON dept
FOR EACH ROW
-- Before row is deleted from dept or 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;
The following trigger on the dept table enforces the DELETE CASCADE referential action on the primary key of the dept table:
CREATE OR REPLACE TRIGGER Dept_del_cascade
AFTER DELETE ON dept
FOR EACH ROW
-- Before row is deleted from dept,
-- delete all rows from emp table whose DEPTNO is same as
-- DEPTNO being deleted from 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.
The following trigger ensures that if a department number is updated in the dept table, then this change is propagated to dependent foreign keys in the emp table:
-- Generate sequence number to be used as flag -- for determining if update occurred on column: CREATE SEQUENCE Update_sequence INCREMENT BY 1 MAXVALUE 5000 CYCLE; CREATE OR REPLACE PACKAGE Integritypackage AS Updateseq NUMBER; END Integritypackage; CREATE OR REPLACE PACKAGE BODY Integritypackage AS END Integritypackage; -- Create flag col: ALTER TABLE emp ADD Update_id NUMBER; CREATE OR REPLACE TRIGGER Dept_cascade1 BEFORE UPDATE OF Deptno ON dept DECLARE
-- Before updating dept table (this is a statement trigger), -- generate new sequence number -- & assign it to public variable UPDATESEQ of -- user-defined package named INTEGRITYPACKAGE: BEGIN Integritypackage.Updateseq := Update_sequence.NEXTVAL;
END;
CREATE OR REPLACE TRIGGER Dept_cascade2
AFTER DELETE OR UPDATE OF Deptno ON dept
FOR EACH ROW
-- For each department number in dept that is updated,
-- cascade update to dependent foreign keys in emp table.
-- Cascade update only if child row was not already 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 3rd trigger
fired by same triggering statement */
END IF;
IF DELETING THEN
-- Before row is deleted from dept,
-- delete all rows from emp table whose DEPTNO is same as
-- DEPTNO being deleted from dept table:
DELETE FROM emp
WHERE emp.Deptno = :OLD.Deptno;
END IF;
END;
CREATE OR REPLACE 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, also fires. The resulting mutating table error is trapped by the Emp_dept_check trigger. Carefully test any triggers that require error trapping to succeed to ensure that they always work properly in your environment.
Triggers can enforce integrity rules other than referential integrity. For example, this trigger performs a complex check before allowing the triggering statement to run.
Note:
You might need to set up the following data structures for the example to work:
CREATE OR REPLACE TABLE Salgrade ( Grade NUMBER, Losal NUMBER, Hisal NUMBER, Job_classification NUMBER);
CREATE OR REPLACE TRIGGER Salary_check
BEFORE INSERT OR UPDATE OF Sal, Job ON Emp99
FOR EACH ROW
DECLARE
Minsal NUMBER;
Maxsal NUMBER;
Salary_out_of_range EXCEPTION;
BEGIN
/* Retrieve minimum & maximum salary for employee's new job classification
from SALGRADE table into MINSAL and MAXSAL: */
SELECT Minsal, Maxsal INTO Minsal, Maxsal
FROM Salgrade
WHERE Job_classification = :NEW.Job;
/* If employee's new salary is less than or greater than
job classification's limits, raise exception.
Exception message is returned and 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.Ename);
WHEN NO_DATA_FOUND THEN
Raise_application_error(-20322,
'Invalid Job Classification '
||:NEW.Job_classification);
END;
Triggers are commonly used to enforce complex security authorizations for table data. Only use triggers to enforce complex security authorizations that cannot be defined using the database security features provided with the database. For example, a trigger can prohibit updates to salary data of the emp table during weekends, holidays, and nonworking hours.
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:
This example shows a trigger used to enforce security.
Note:
You might need to set up the following data structures for the example to work:
CREATE TABLE Company_holidays (Day DATE);
CREATE OR REPLACE TRIGGER Emp_permit_changes
BEFORE INSERT OR DELETE OR UPDATE ON Emp99
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); -- Discard 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,'Might not change '
||'employee table during the weekend');
WHEN Not_on_holidays THEN
Raise_application_error(-20325,'Might not change '
||'employee table during a holiday');
WHEN Non_working_hours THEN
Raise_application_error(-20326,'Might not change '
||'emp table during nonworking hours');
END;
Triggers are very useful when you want to transparently perform a related change in the database following certain events.
The REORDER trigger example shows a trigger that reorders parts as necessary when certain conditions are met. (In other words, a triggering statement is entered, and the PARTS_ON_HAND value is less than the REORDER_POINT value.)
Triggers can derive column values automatically, based upon a value provided by an INSERT or UPDATE statement. This type of trigger is useful to force values in specific columns that depend on the values of other columns in the same row. BEFORE row triggers are necessary to complete this type of operation for the following reasons:
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.
Note:
You might need to set up the following data structures for the example to work:
ALTER TABLE Emp99 ADD( Uppername VARCHAR2(20), Soundexname VARCHAR2(20));
CREATE OR REPLACE TRIGGER Derived BEFORE INSERT OR UPDATE OF Ename ON Emp99 /* Before updating the ENAME field, derive the values for the UPPERNAME and SOUNDEXNAME fields. Restrict users from updating these fields directly: */ FOR EACH ROW BEGIN :NEW.Uppername := UPPER(:NEW.Ename); :NEW.Soundexname := SOUNDEX(:NEW.Ename); END;
Views are an excellent mechanism to provide logical windows over table data. However, when the view query gets complex, the system implicitly cannot translate the DML on the view into those on the underlying tables. INSTEAD OF triggers help solve this problem. These triggers can be defined over views, and they fire instead of the actual DML.
Consider a library system where books are arranged under their respective titles. The library consists of a collection of book type objects. The following example explains the schema.
CREATE OR REPLACE TYPE Book_t AS OBJECT ( Booknum NUMBER, Title VARCHAR2(20), Author VARCHAR2(20), Available CHAR(1) ); CREATE OR REPLACE TYPE Book_list_t AS TABLE OF Book_t;
Assume that the following tables exist in the relational schema:
Table Book_table (Booknum, Section, Title, Author, Available)
| Booknum | Section | Title | Author | Available |
|---|---|---|---|---|
| 121001 | Classic | Iliad | Homer | Y |
| 121002 | Novel | Gone with the Wind | Mitchell M | N |
Library consists of library_table(section).
| Section |
|---|
| Geography |
| Classic |
You can define a complex view over these tables to create a logical view of the library with sections and a collection of books in each section.
CREATE OR REPLACE VIEW Library_view AS SELECT i.Section, CAST (MULTISET ( SELECT b.Booknum, b.Title, b.Author, b.Available FROM Book_table b WHERE b.Section = i.Section) AS Book_list_t) BOOKLIST FROM Library_table i;
Make this view updatable by defining an INSTEAD OF trigger over the view.
CREATE OR REPLACE TRIGGER Library_trigger INSTEAD OF INSERT ON Library_view FOR EACH ROW
Bookvar BOOK_T;
i INTEGER;
BEGIN
INSERT INTO Library_table VALUES (:NEW.Section);
FOR i IN 1..:NEW.Booklist.COUNT LOOP
Bookvar := Booklist(i);
INSERT INTO book_table
VALUES ( Bookvar.booknum, :NEW.Section, Bookvar.Title, Bookvar.Author, bookvar.Available);
END LOOP;
END;
/
The library_view is an updatable view, and any INSERTs on the view are handled by the trigger that fires automatically. For example:
INSERT INTO Library_view VALUES ('History', book_list_t(book_t(121330, 'Alexander', 'Mirth', 'Y');
Similarly, you can also define triggers on the nested table booklist to handle modification of the nested table element.
You can use LOGON triggers to execute the package associated with an application context. An application context captures session-related information about the user who is logging in to the database. From there, your application can control how much access this user has, based on his or her session information.
Note:
If you have very specific logon requirements, such as preventing users from logging in from outside the firewall or after work hours, consider using Oracle Database Vault instead of LOGON triggers. With Oracle Database Vault, you can create custom rules to strictly control user access.
