DML Triggers
A DML trigger is created on either a table or view, and its triggering event is composed of the DML statements DELETE, INSERT, and UPDATE.
To create a trigger that fires in response to a MERGE statement, create triggers on the INSERT and UPDATE statements to which the MERGE operation decomposes.
A DML trigger is either simple or compound.
A simple DML trigger fires at exactly one of these timing points:
-
Before the triggering statement runs
(The trigger is called a
BEFOREstatement trigger or statement-levelBEFOREtrigger.) -
After the triggering statement runs
(The trigger is called an
AFTERstatement trigger or statement-levelAFTERtrigger.) -
Before each row that the triggering statement affects
(The trigger is called a
BEFOREeach row trigger or row-levelBEFOREtrigger.) -
After each row that the triggering statement affects
(The trigger is called an
AFTEReach row trigger or row-levelAFTERtrigger.)
When a trigger is created on an INSERT statement with
FORALL, the inserts are treated as a single operation. This means
that all statement level triggers fire only once, not for each insert. When a trigger is
created on an UPDATE or DELETE statement with
FORALL, the trigger is executed for each DML statement. This
results in better performance for insert operations.
A compound DML trigger created on a table or editioning view can fire at one, some, or all of the preceding timing points. Compound DML triggers help program an approach where you want the actions that you implement for the various timing points to share common data.
A simple or compound DML trigger that fires at row level can access the data in the row that it is processing. For details, see "Correlation Names and Pseudorecords".
An INSTEAD OF DML trigger is a DML trigger created on either a noneditioning view or a nested table column of a noneditioning view.
Except in an INSTEAD OF trigger, a triggering UPDATE statement can include a column list. With a column list, the trigger fires only when a specified column is updated. Without a column list, the trigger fires when any column of the associated table is updated.
Topics
Conditional Predicates for Detecting Triggering DML Statement
The triggering event of a DML trigger can be composed of multiple triggering statements. When one of them fires the trigger, the trigger can determine which one by using these conditional predicates.
Table 10-1 Conditional Predicates
| Conditional Predicate | TRUE if and only if: |
|---|---|
|
|
An |
|
|
An |
|
|
An |
|
|
A |
A conditional predicate can appear wherever a BOOLEAN expression can appear.
Example 10-1 Trigger Uses Conditional Predicates to Detect Triggering Statement
This example creates a DML trigger that uses conditional predicates to determine which of its four possible triggering statements fired it.
CREATE OR REPLACE TRIGGER t
BEFORE
INSERT OR
UPDATE OF salary, department_id OR
DELETE
ON employees
BEGIN
CASE
WHEN INSERTING THEN
DBMS_OUTPUT.PUT_LINE('Inserting');
WHEN UPDATING('salary') THEN
DBMS_OUTPUT.PUT_LINE('Updating salary');
WHEN UPDATING('department_id') THEN
DBMS_OUTPUT.PUT_LINE('Updating department ID');
WHEN DELETING THEN
DBMS_OUTPUT.PUT_LINE('Deleting');
END CASE;
END;
/INSTEAD OF DML Triggers
An INSTEAD OF DML trigger is a DML trigger created on a noneditioning view, or on a nested table column of a noneditioning view. The database fires the INSTEAD OF trigger instead of running the triggering DML statement.
An INSTEAD OF trigger cannot be conditional.
An INSTEAD OF trigger is the only way to update a view that is not inherently updatable. Design the INSTEAD OF trigger to determine what operation was intended and do the appropriate DML operations on the underlying tables.
An INSTEAD OF trigger is always a row-level trigger. An INSTEAD OF trigger can read OLD and NEW values, but cannot change them.
An INSTEAD OF trigger with the NESTED TABLE clause fires only if the triggering statement operates on the elements of the specified nested table column of the view. The trigger fires for each modified nested table element.
See Also:
-
Oracle Database SQL Language Reference for information about inherently updatable views
-
"Compound DML Trigger Structure" for information about compound DML triggers with the
INSTEADOFEACHROWsection
Example 10-2 INSTEAD OF Trigger
This example creates the view oe.order_info to display information about customers and their orders. The view is not inherently updatable (because the primary key of the orders table, order_id, is not unique in the result set of the join view). The example creates an INSTEAD OF trigger to process INSERT statements directed to the view. The trigger inserts rows into the base tables of the view, customers and orders.
CREATE OR REPLACE VIEW order_info AS
SELECT c.customer_id, c.cust_last_name, c.cust_first_name,
o.order_id, o.order_date, o.order_status
FROM customers c, orders o
WHERE c.customer_id = o.customer_id;
CREATE OR REPLACE TRIGGER order_info_insert
INSTEAD OF INSERT ON order_info
DECLARE
duplicate_info EXCEPTION;
PRAGMA EXCEPTION_INIT (duplicate_info, -00001);
BEGIN
INSERT INTO customers
(customer_id, cust_last_name, cust_first_name)
VALUES (
:new.customer_id,
:new.cust_last_name,
:new.cust_first_name);
INSERT INTO orders (order_id, order_date, customer_id)
VALUES (
:new.order_id,
:new.order_date,
:new.customer_id);
EXCEPTION
WHEN duplicate_info THEN
RAISE_APPLICATION_ERROR (
num=> -20107,
msg=> 'Duplicate customer or order ID');
END order_info_insert;
/
Query to show that row to be inserted does not exist:
SELECT COUNT(*) FROM order_info WHERE customer_id = 999;
Result:
COUNT(*)
----------
0
1 row selected.
Insert row into view:
INSERT INTO order_info VALUES
(999, 'Smith', 'John', 2500, TO_DATE('13-MAR-2001', 'DD-MON-YYYY'), 0);
Result:
1 row created.
Query to show that row has been inserted in view:
SELECT COUNT(*) FROM order_info WHERE customer_id = 999;
Result:
COUNT(*)
----------
1
1 row selected.
Query to show that row has been inserted in customers table:
SELECT COUNT(*) FROM customers WHERE customer_id = 999;
Result:
COUNT(*)
----------
1
1 row selected.
Query to show that row has been inserted in orders table:
SELECT COUNT(*) FROM orders WHERE customer_id = 999;
Result:
COUNT(*)
----------
1
1 row selected.Example 10-3 INSTEAD OF Trigger on Nested Table Column of View
In this example, the view dept_view contains a nested table of employees, emplist, created by the CAST function (described in Oracle Database SQL Language Reference). To modify the emplist column, the example creates an INSTEAD OF trigger on the column.
-- Create type of nested table element:
CREATE OR REPLACE TYPE nte
AUTHID DEFINER IS
OBJECT (
emp_id NUMBER(6),
lastname VARCHAR2(25),
job VARCHAR2(10),
sal NUMBER(8,2)
);
/
-- Created type of nested table:
CREATE OR REPLACE TYPE emp_list_ IS
TABLE OF nte;
/
-- Create view:
CREATE OR REPLACE VIEW dept_view AS
SELECT d.department_id,
d.department_name,
CAST (MULTISET (SELECT e.employee_id, e.last_name, e.job_id, e.salary
FROM employees e
WHERE e.department_id = d.department_id
)
AS emp_list_
) emplist
FROM departments d;
-- Create trigger:
CREATE OR REPLACE TRIGGER dept_emplist_tr
INSTEAD OF INSERT ON NESTED TABLE emplist OF dept_view
REFERENCING NEW AS Employee
PARENT AS Department
FOR EACH ROW
BEGIN
-- Insert on nested table translates to insert on base table:
INSERT INTO employees (
employee_id,
last_name,
email,
hire_date,
job_id,
salary,
department_id
)
VALUES (
:Employee.emp_id, -- employee_id
:Employee.lastname, -- last_name
:Employee.lastname || '@example.com', -- email
SYSDATE, -- hire_date
:Employee.job, -- job_id
:Employee.sal, -- salary
:Department.department_id -- department_id
);
END;
/
Query view before inserting row into nested table:
SELECT emplist FROM dept_view WHERE department_id=10;
Result:
EMPLIST(EMP_ID, LASTNAME, JOB, SAL) ---------------------------------------------- EMP_LIST_(NTE(200, 'Whalen', 'AD_ASST', 4200)) 1 row selected.
Query table before inserting row into nested table:
SELECT employee_id, last_name, job_id, salary FROM employees WHERE department_id = 10;
Result:
EMPLOYEE_ID LAST_NAME JOB_ID SALARY
----------- ------------------------- ---------- ----------
200 Whalen AD_ASST 4200
1 row selected.
Insert a row into nested table:
INSERT INTO TABLE ( SELECT d.emplist FROM dept_view d WHERE department_id = 10 ) VALUES (1001, 'Glenn', 'AC_MGR', 10000);
Query view after inserting row into nested table:
SELECT emplist FROM dept_view WHERE department_id=10;
Result (formatted to fit page):
EMPLIST(EMP_ID, LASTNAME, JOB, SAL)
--------------------------------------------------------------------------------
EMP_LIST_(NTE(200, 'Whalen', 'AD_ASST', 4200),
NTE(1001, 'Glenn', 'AC_MGR', 10000))
1 row selected.
Query table after inserting row into nested table:
SELECT employee_id, last_name, job_id, salary FROM employees WHERE department_id = 10;
Result:
EMPLOYEE_ID LAST_NAME JOB_ID SALARY
----------- ------------------------- ---------- ----------
200 Whalen AD_ASST 4200
1001 Glenn AC_MGR 10000
2 rows selected.Compound DML Triggers
A compound DML trigger created on a table or editioning view can fire at multiple timing points. Each timing point section has its own executable part and optional exception-handling part, but all of these parts can access a common PL/SQL state. The common state is established when the triggering statement starts and is destroyed when the triggering statement completes, even when the triggering statement causes an error.
A compound DML trigger created on a noneditioning view is not really compound, because it has only one timing point section.
A compound trigger can be conditional, but not autonomous.
Two common uses of compound triggers are:
-
To accumulate rows destined for a second table so that you can periodically bulk-insert them
-
To avoid the mutating-table error (ORA-04091)
Topics
Compound DML Trigger Structure
The optional declarative part of a compound trigger declares variables and subprograms that all of its timing-point sections can use. When the trigger fires, the declarative part runs before any timing-point sections run. The variables and subprograms exist for the duration of the triggering statement.
A compound DML trigger created on a noneditioning view is not really compound, because it has only one timing point section. The syntax for creating the simplest compound DML trigger on a noneditioning view is:
CREATE trigger FOR dml_event_clause ON view COMPOUND TRIGGER INSTEAD OF EACH ROW IS BEGIN statement; END INSTEAD OF EACH ROW;
A compound DML trigger created on a table or editioning view has at least one timing-point section in Table 10-2. If the trigger has multiple timing-point sections, they can be in any order, but no timing-point section can be repeated. If a timing-point section is absent, then nothing happens at its timing point.
Table 10-2 Compound Trigger Timing-Point Sections
| Timing Point | Section |
|---|---|
|
Before the triggering statement runs |
|
|
After the triggering statement runs |
|
|
Before each row that the triggering statement affects |
|
|
After each row that the triggering statement affects |
|
See Also:
"CREATE TRIGGER Statement" for more information about the syntax of compound triggers
A compound DML trigger does not have an initialization section, but the BEFORE STATEMENT section, which runs before any other timing-point section, can do any necessary initialization.
If a compound DML trigger has neither a BEFORE STATEMENT section nor an AFTER STATEMENT section, and its triggering statement affects no rows, then the trigger never fires.
Compound DML Trigger Restrictions
In addition to the "Trigger Restrictions"), compound DML triggers have these restrictions:
-
OLD,NEW, andPARENTcannot appear in the declarative part, theBEFORESTATEMENTsection, or theAFTERSTATEMENTsection. -
Only the
BEFOREEACHROWsection can change the value ofNEW. -
A timing-point section cannot handle exceptions raised in another timing-point section.
-
If a timing-point section includes a
GOTOstatement, the target of theGOTOstatement must be in the same timing-point section.
Performance Benefit of Compound DML Triggers
A compound DML trigger has a performance benefit when the triggering statement affects many rows.
For example, suppose that this statement triggers a compound DML trigger that has all four timing-point sections in Table 10-2:
INSERT INTO Target SELECT c1, c2, c3 FROM Source WHERE Source.c1 > 0
Although the BEFORE EACH ROW and AFTER EACH ROW sections of the trigger run for each row of Source whose column c1 is greater than zero, the BEFORE STATEMENT section runs only before the INSERT statement runs and the AFTER STATEMENT section runs only after the INSERT statement runs.
A compound DML trigger has a greater performance benefit when it uses bulk SQL, described in "Bulk SQL and Bulk Binding".
Using Compound DML Triggers with Bulk Insertion
A compound DML trigger is useful for accumulating rows destined for a second table so that you can periodically bulk-insert them. To get the performance benefit from the compound trigger, you must specify BULK COLLECT INTO in the FORALL statement (otherwise, the FORALL statement does a single-row DML operation multiple times). For more information about using the BULK COLLECT clause with the FORALL statement, see "Using FORALL Statement and BULK COLLECT Clause Together".
See Also:
Scenario: You want to log every change to hr.employees.salary in a new table, employee_salaries. A single UPDATE statement updates many rows of the table hr.employees; therefore, bulk-inserting rows into employee.salaries is more efficient than inserting them individually.
Solution: Define a compound trigger on updates of the table hr.employees, as in Example 10-4. You do not need a BEFORE STATEMENT section to initialize idx or salaries, because they are state variables, which are initialized each time the trigger fires (even when the triggering statement is interrupted and restarted).
Note:
To run Example 10-4, you must have the EXECUTE privilege on the package DBMS_LOCK.
Example 10-4 Compound Trigger Logs Changes to One Table in Another Table
CREATE TABLE employee_salaries (
employee_id NUMBER NOT NULL,
change_date DATE NOT NULL,
salary NUMBER(8,2) NOT NULL,
CONSTRAINT pk_employee_salaries PRIMARY KEY (employee_id, change_date),
CONSTRAINT fk_employee_salaries FOREIGN KEY (employee_id)
REFERENCES employees (employee_id)
ON DELETE CASCADE)
/
CREATE OR REPLACE TRIGGER maintain_employee_salaries
FOR UPDATE OF salary ON employees
COMPOUND TRIGGER
-- Declarative Part:
-- Choose small threshhold value to show how example works:
threshhold CONSTANT SIMPLE_INTEGER := 7;
TYPE salaries_t IS TABLE OF employee_salaries%ROWTYPE INDEX BY SIMPLE_INTEGER;
salaries salaries_t;
idx SIMPLE_INTEGER := 0;
PROCEDURE flush_array IS
n CONSTANT SIMPLE_INTEGER := salaries.count();
BEGIN
FORALL j IN 1..n
INSERT INTO employee_salaries VALUES salaries(j);
salaries.delete();
idx := 0;
DBMS_OUTPUT.PUT_LINE('Flushed ' || n || ' rows');
END flush_array;
-- AFTER EACH ROW Section:
AFTER EACH ROW IS
BEGIN
idx := idx + 1;
salaries(idx).employee_id := :NEW.employee_id;
salaries(idx).change_date := SYSTIMESTAMP;
salaries(idx).salary := :NEW.salary;
IF idx >= threshhold THEN
flush_array();
END IF;
END AFTER EACH ROW;
-- AFTER STATEMENT Section:
AFTER STATEMENT IS
BEGIN
flush_array();
END AFTER STATEMENT;
END maintain_employee_salaries;
/
Increase salary of every employee in department 50 by 10%:
UPDATE employees
SET salary = salary * 1.1
WHERE department_id = 50
/
Result:
Flushed 7 rows
Flushed 7 rows
Flushed 7 rows
Flushed 7 rows
Flushed 7 rows
Flushed 7 rows
Flushed 3 rows
45 rows updated.
Wait two seconds:
BEGIN
DBMS_SESSION.SLEEP(2);
END;
/
Increase salary of every employee in department 50 by 5%:
UPDATE employees
SET salary = salary * 1.05
WHERE department_id = 50
/
Result:
Flushed 7 rows
Flushed 7 rows
Flushed 7 rows
Flushed 7 rows
Flushed 7 rows
Flushed 7 rows
Flushed 3 rows
45 rows updated.
See changes to employees table reflected in employee_salaries table:
SELECT employee_id, count(*) c
FROM employee_salaries
GROUP BY employee_id
/
Result:
EMPLOYEE_ID C
----------- ----------
120 2
121 2
122 2
123 2
124 2
125 2
...
199 2
45 rows selected.Using Compound DML Triggers to Avoid Mutating-Table Error
A compound DML trigger is useful for avoiding the mutating-table error (ORA-04091) explained in "Mutating-Table Restriction".
Scenario: A business rule states that an employee's salary increase must not exceed 10% of the average salary for the employee's department. This rule must be enforced by a trigger.
Solution: Define a compound trigger on updates of the table hr.employees, as in Example 10-5. The state variables are initialized each time the trigger fires (even when the triggering statement is interrupted and restarted).
Example 10-5 Compound Trigger Avoids Mutating-Table Error
CREATE OR REPLACE TRIGGER Check_Employee_Salary_Raise
FOR UPDATE OF Salary ON Employees
COMPOUND TRIGGER
Ten_Percent CONSTANT NUMBER := 0.1;
TYPE Salaries_t IS TABLE OF Employees.Salary%TYPE;
Avg_Salaries Salaries_t;
TYPE Department_IDs_t IS TABLE OF Employees.Department_ID%TYPE;
Department_IDs Department_IDs_t;
-- Declare collection type and variable:
TYPE Department_Salaries_t IS TABLE OF Employees.Salary%TYPE
INDEX BY VARCHAR2(80);
Department_Avg_Salaries Department_Salaries_t;
BEFORE STATEMENT IS
BEGIN
SELECT AVG(e.Salary), NVL(e.Department_ID, -1)
BULK COLLECT INTO Avg_Salaries, Department_IDs
FROM Employees e
GROUP BY e.Department_ID;
FOR j IN 1..Department_IDs.COUNT() LOOP
Department_Avg_Salaries(Department_IDs(j)) := Avg_Salaries(j);
END LOOP;
END BEFORE STATEMENT;
AFTER EACH ROW IS
BEGIN
IF :NEW.Salary - :Old.Salary >
Ten_Percent*Department_Avg_Salaries(:NEW.Department_ID)
THEN
Raise_Application_Error(-20000, 'Raise too big');
END IF;
END AFTER EACH ROW;
END Check_Employee_Salary_Raise;Triggers for Ensuring Referential Integrity
You can use triggers and constraints to maintain referential integrity between parent and child tables, as Table 10-3 shows. (For more information about constraints, see Oracle Database SQL Language Reference.)
Table 10-3 Constraints and Triggers for Ensuring Referential Integrity
| Table | Constraint to Declare on Table | Triggers to Create on Table |
|---|---|---|
|
Parent |
|
One or more triggers that ensure that when No action is required for inserts into the parent table, because no dependent foreign keys exist. |
|
Child |
Disable this foreign key constraint to prevent the corresponding |
One trigger that ensures that values inserted or updated in the |
Topics
Note:
The examples in the following topics use these tables, which share the column Deptno:
CREATE TABLE emp (
Empno NUMBER NOT NULL,
Ename VARCHAR2(10),
Job VARCHAR2(9),
Mgr NUMBER(4),
Hiredate DATE,
Sal NUMBER(7,2),
Comm NUMBER(7,2),
Deptno NUMBER(2) NOT NULL);
CREATE TABLE dept (
Deptno NUMBER(2) NOT NULL,
Dname VARCHAR2(14),
Loc VARCHAR2(13),
Mgr_no NUMBER,
Dept_type NUMBER);
Several triggers include statements that lock rows (SELECT FOR UPDATE). This operation is necessary to maintain concurrency while the rows are being processed.
These examples are not meant to be used exactly as written. They are provided to assist you in designing your own triggers.
Foreign Key Trigger for Child Table
The trigger in Example 10-6 ensures that before an INSERT or UPDATE statement affects a foreign key value, the corresponding value exists in the parent key. The exception ORA-04091 (mutating-table error) allows the trigger emp_dept_check to be used with the UPDATE_SET_DEFAULT and UPDATE_CASCADE triggers. This exception is unnecessary if the trigger emp_dept_check is used alone.
Example 10-6 Foreign Key Trigger for Child Table
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 (Invalid_department, -4093);
PRAGMA EXCEPTION_INIT (Valid_department, -4092);
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 code 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 Parent Table
The trigger in Example 10-7 enforces the UPDATE and DELETE RESTRICT referential action on the primary key of the dept table.
Caution:
The trigger in Example 10-7 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 when A fires B, which fires A).
Example 10-7 UPDATE and DELETE RESTRICT Trigger for Parent 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;
PRAGMA EXCEPTION_INIT (employees_present, -4094);
PRAGMA EXCEPTION_INIT (employees_not_present, -4095);
-- 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 code 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;UPDATE and DELETE SET NULL Trigger for Parent Table
The trigger in Example 10-8 enforces the UPDATE and DELETE SET NULL referential action on the primary key of the dept table.
Example 10-8 UPDATE and DELETE SET NULL Trigger for Parent 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;
/DELETE CASCADE Trigger for Parent Table
The trigger in Example 10-9 enforces the DELETE CASCADE referential action on the primary key of the dept table.
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.
Example 10-9 DELETE CASCADE Trigger for Parent 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; /
UPDATE CASCADE Trigger for Parent Table
The triggers in Example 10-10 ensure that if a department number is updated in the dept table, then this change is propagated to dependent foreign keys in the emp table.
Note:
Because the trigger dept_cascade2 updates the emp table, the emp_dept_check trigger in Example 10-6, 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.
Example 10-10 UPDATE CASCADE Trigger for Parent 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 AUTHID DEFINER 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 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 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
-- After 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;
/Triggers for Complex Constraint Checking
Triggers can enforce integrity rules other than referential integrity. The trigger in Example 10-11 does a complex check before allowing the triggering statement to run.
Note:
Example 10-11 needs this data structure:
CREATE TABLE Salgrade (
Grade NUMBER,
Losal NUMBER,
Hisal NUMBER,
Job_classification VARCHAR2(9));Example 10-11 Trigger Checks Complex Constraints
CREATE OR REPLACE 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;
PRAGMA EXCEPTION_INIT (Salary_out_of_range, -4096);
BEGIN
/* Retrieve minimum & maximum salary for employee's new job classification
from SALGRADE table into MINSAL and MAXSAL: */
SELECT Losal, Hisal 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');
END;
/Triggers for Complex Security Authorizations
Triggers are commonly used to enforce complex security authorizations for table data. Use triggers only to enforce complex security authorizations that you cannot define using the database security features provided with the database. For example, use a trigger to prohibit updates to the employee table during weekends 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:
-
The security check is done before the triggering statement is allowed to run, so that no wasted work is done by an unauthorized statement.
-
The security check is done only for the triggering statement, not for each row affected by the triggering statement.
The trigger in Example 10-12 enforces security by raising exceptions when anyone tries to update the table employees during weekends or nonworking hours.
See Also:
Oracle Database Security Guide for detailed information about database security features
Example 10-12 Trigger Enforces Security Authorizations
CREATE OR REPLACE TRIGGER Employee_permit_changes
BEFORE INSERT OR DELETE OR UPDATE ON employees
DECLARE
Dummy INTEGER;
Not_on_weekends EXCEPTION;
Nonworking_hours EXCEPTION;
PRAGMA EXCEPTION_INIT (Not_on_weekends, -4097);
PRAGMA EXCEPTION_INIT (Nonworking_hours, -4099);
BEGIN
-- Check for weekends:
IF (TO_CHAR(Sysdate, 'DAY') = 'SAT' OR
TO_CHAR(Sysdate, 'DAY') = 'SUN') THEN
RAISE Not_on_weekends;
END IF;
-- Check for work hours (8am to 6pm):
IF (TO_CHAR(Sysdate, 'HH24') < 8 OR
TO_CHAR(Sysdate, 'HH24') > 18) THEN
RAISE Nonworking_hours;
END IF;
EXCEPTION
WHEN Not_on_weekends THEN
Raise_application_error(-20324,'Might not change '
||'employee table during the weekend');
WHEN Nonworking_hours THEN
Raise_application_error(-20326,'Might not change '
||'emp table during Nonworking hours');
END;
/Triggers for Transparent Event Logging
Triggers are very useful when you want to transparently do 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 for Deriving Column Values
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 these reasons:
-
The dependent values must be derived before the
INSERTorUPDATEoccurs, so that the triggering statement can use the derived values. -
The trigger must fire for each row affected by the triggering
INSERTorUPDATEstatement.
The trigger in Example 10-13 derives new column values for a table whenever a row is inserted or updated.
Note:
Example 10-13 needs this change to this data structure:
ALTER TABLE Emp ADD(
Uppername VARCHAR2(20),
Soundexname VARCHAR2(20));Example 10-13 Trigger Derives New Column Values
CREATE OR REPLACE TRIGGER Derived BEFORE INSERT OR UPDATE OF Ename ON Emp /* 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; /
Triggers for Building Complex Updatable Views
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 by title. The library consists of a collection of book type objects:
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; /
The table Book_table is created and populated like this:
DROP TABLE Book_table; CREATE TABLE Book_table ( Booknum NUMBER, Section VARCHAR2(20), Title VARCHAR2(20), Author VARCHAR2(20), Available CHAR(1) ); INSERT INTO Book_table ( Booknum, Section, Title, Author, Available ) VALUES ( 121001, 'Classic', 'Iliad', 'Homer', 'Y' ); INSERT INTO Book_table ( Booknum, Section, Title, Author, Available ) VALUES ( 121002, 'Novel', 'Gone with the Wind', 'Mitchell M', 'N' ); SELECT * FROM Book_table ORDER BY Booknum;
Result:
BOOKNUM SECTION TITLE AUTHOR A
---------- -------------------- -------------------- -------------------- -
121001 Classic Iliad Homer Y
121002 Novel Gone with the Wind Mitchell M N
2 rows selected.
The table Library_table is created and populated like this:
DROP TABLE Library_table;
CREATE TABLE Library_table (Section VARCHAR2(20));
INSERT INTO Library_table (Section)
VALUES ('Novel');
INSERT INTO Library_table (Section)
VALUES ('Classic');
SELECT * FROM Library_table ORDER BY Section;
Result:
SECTION -------------------- Classic Novel 2 rows selected.
You can define a complex view over the tables Book_table and Library_table 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;
(For information about the CAST function, see Oracle Database SQL Language Reference.)
Make Library_view updatable by defining an INSTEAD OF trigger on it:
CREATE OR REPLACE TRIGGER Library_trigger
INSTEAD OF
INSERT ON Library_view
FOR EACH ROW
DECLARE
Bookvar Book_t;
i INTEGER;
BEGIN
INSERT INTO Library_table
VALUES (:NEW.Section);
FOR i IN 1..:NEW.Booklist.COUNT LOOP
Bookvar := :NEW.Booklist(i);
INSERT INTO Book_table (
Booknum, Section, Title, Author, Available
)
VALUES (
Bookvar.booknum, :NEW.Section, Bookvar.Title,
Bookvar.Author, bookvar.Available
);
END LOOP;
END;
/
Insert a new row into Library_view:
INSERT INTO Library_view (Section, Booklist) VALUES ( 'History', book_list_t (book_t (121330, 'Alexander', 'Mirth', 'Y')) );
See the effect on Library_view:
SELECT * FROM Library_view ORDER BY Section;
Result:
SECTION -------------------- BOOKLIST(BOOKNUM, TITLE, AUTHOR, AVAILABLE) -------------------------------------------------------------------- Classic BOOK_LIST_T(BOOK_T(121001, 'Iliad', 'Homer', 'Y')) History BOOK_LIST_T(BOOK_T(121330, 'Alexander', 'Mirth', 'Y')) Novel BOOK_LIST_T(BOOK_T(121002, 'Gone with the Wind', 'Mitchell M', 'N')) 3 rows selected.
See the effect on Book_table:
SELECT * FROM Book_table ORDER BY Booknum;
Result:
BOOKNUM SECTION TITLE AUTHOR A
---------- -------------------- -------------------- -------------------- -
121001 Classic Iliad Homer Y
121002 Novel Gone with the Wind Mitchell M N
121330 History Alexander Mirth Y
3 rows selected.
See the effect on Library_table:
SELECT * FROM Library_table ORDER BY Section;
Result:
SECTION -------------------- Classic History Novel 3 rows selected.
Similarly, you can also define triggers on the nested table booklist to handle modification of the nested table element.
Triggers for Fine-Grained Access Control
You can use LOGON triggers to run 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 their 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.
See Also:
-
Oracle Database Security Guide for information about creating a
LOGONtrigger to run a database session application context package -
Oracle Database Vault Administrator's Guide for information about Oracle Database Vault