Oracle Instead Of Triggers

Oracle Instead-Of Triggers Version 11.2.0.3

General Information

Notes on Updatable Views

An updatable view is one you can use to insert, update, or delete base table rows. You can create a view to be inherently updatable, or you can create an INSTEAD OF trigger on any view to make it updatable.

To learn whether and in what ways the columns of an inherently updatable view can be modified, query the USER_UPDATABLE_COLUMNS data dictionary view. The information displayed by this view is meaningful only for inherently updatable views. For a view to be inherently updatable, the following conditions must be met:

Each column in the view must map to a column of a single table. For example, if a view column maps to the output of a TABLE clause (an unnested collection), then the view is not inherently updatable.
The view must not contain any of the following constructs:
1. A set operator
2. A DISTINCT operator
3. An aggregate or analytic function
4. A GROUP BY, ORDER BY, MODEL, CONNECT BY, or START WITH clause
5. A collection expression in a SELECT list
6. A subquery in a SELECT list
7. A subquery designated WITH READ ONLY
8. Joins, with some exceptions, as documented in Oracle Database Administrator's Guide
In addition, if an inherently updatable view contains pseudocolumns or expressions, then you cannot update base table rows with an UPDATE statement that refers to any of these pseudocolumns or expressions.
If you want a join view to be updatable, then all of the following conditions must be true:
1. The DML statement must affect only one table underlying the join.
2. For an INSERT statement, the view must not be created WITH CHECK OPTION, and all columns into which values are inserted must come from a key-preserved table. A key-preserved table is one for which every primary key or unique key value in the base table is also unique in the join view.
3. For an UPDATE statement, all columns updated must be extracted from a key-preserved table. If the view was created WITH CHECK OPTION, then join columns and columns taken from tables that are referenced more than once in the view must be shielded from UPDATE.
For a DELETE statement, if the join results in more than one key-preserved table, then Oracle Database deletes from the first table named in the FROM clause, whether or not the view was created WITH CHECK OPTION.

Related Data Dictionary Objects

ALL_TRIGGERS	TRIGGER$	USER_TRIGGERS
DBA_TRIGGERS

Related System Privileges

ALTER ANY TRIGGER
CREATE ANY TRIGGER
CREATE TRIGGER
DROP ANY TRIGGER

Demonstration Setup

Demo Tables And Constraints

conn uwclass/uwclass

CREATE TABLE employee (
employee_no   VARCHAR2(8),
last_name     VARCHAR2(25) NOT NULL,
first_name    VARCHAR2(10) NOT NULL,
dept_code     VARCHAR2(3) NOT NULL,
active_flag   VARCHAR2(1) DEFAULT 'Y',
mod_user_id   VARCHAR2(30) DEFAULT USER,
mod_user_date DATE DEFAULT SYSDATE);

CREATE TABLE permission_code (
pcode             VARCHAR2(2),
pcode_description VARCHAR2(40) NOT NULL,
mod_user_id       VARCHAR2(30) DEFAULT USER,
mod_user_date     DATE DEFAULT SYSDATE);

CREATE TABLE user_role (
dept_code     VARCHAR2(3),
pcode         VARCHAR2(2),
access_level  VARCHAR2(1) DEFAULT 'R',
mod_user_id   VARCHAR2(30) DEFAULT USER,
mod_user_date DATE DEFAULT SYSDATE);

CREATE TABLE user_permission (
employee_no   VARCHAR2(8),
pcode         VARCHAR2(2),
access_level  VARCHAR2(1) DEFAULT 'R',
mod_user_id   VARCHAR2(30) DEFAULT USER,
mod_user_date DATE DEFAULT SYSDATE);

CREATE TABLE dept_code (
dept_code VARCHAR2(3),
dept_name VARCHAR2(30));

CREATE TABLE test (
test VARCHAR2(20));

Sample Data

INSERT INTO employee
(employee_no, last_name, first_name, dept_code, active_flag)
VALUES
('5001', 'Mark', 'Townsend', 'LCR', 'Y');

INSERT INTO employee
(employee_no, last_name, first_name, dept_code, active_flag)
VALUES
('3996', 'Dacko', 'Carol', 'ESR', 'Y');

INSERT INTO employee
(employee_no, last_name, first_name, dept_code, active_flag)
VALUES
('6842', 'Morgan', 'Daniel', 'ADM', 'Y');

INSERT INTO permission_code
VALUES ('BO', 'BILLING OPTIONS', USER, SYSDATE);

INSERT INTO permission_code
VALUES ('CL', 'CLASS CODES', USER, SYSDATE);

INSERT INTO permission_code
VALUES ('CR', 'CREWS', USER, SYSDATE);

INSERT INTO permission_code
VALUES ('CT', 'CREW TYPES', USER, SYSDATE);

INSERT INTO permission_code
VALUES ('CU', 'CUSTOMER TYPES', USER, SYSDATE);

INSERT INTO permission_code
VALUES ('DH', 'WORKORDER DASH NUMBERS', USER, SYSDATE);

INSERT INTO dept_code (dept_code, dept_name)
VALUES ('ADM', 'ADMINISTRATION');

INSERT INTO dept_code (dept_code, dept_name)
VALUES ('COO', 'COORDINATOR');

INSERT INTO dept_code (dept_code, dept_name)
VALUES ('ESE', 'ELECTRICAL SERVICE');

INSERT INTO dept_code (dept_code, dept_name)
VALUES ('ESR', 'ELECTRICAL SERVICE REP');

INSERT INTO dept_code (dept_code, dept_name)
VALUES ('ENG', 'ENGINEER');

INSERT INTO dept_code (dept_code, dept_name)
VALUES ('LCR', 'LINE CREW');

INSERT INTO dept_code (dept_code, dept_name)
VALUES ('MCR', 'METER CREW');

INSERT INTO dept_code (dept_code, dept_name)
VALUES ('NWE', 'NETWORK ENGINEER');

INSERT INTO dept_code (dept_code, dept_name)
VALUES ('SKA', 'SKETCH ARTIST');

INSERT INTO user_role
(dept_code, pcode, access_level)
SELECT r.dept_code, p.pcode, 'R'
FROM dept_code r, permission_code p;

INSERT INTO user_permission
(employee_no, pcode, access_level)
SELECT e.employee_no, r.pcode, r.access_level
FROM employee e, user_role r
WHERE e.dept_code = r.dept_code;

COMMIT;

Non Key-Preserved Relational Views

-- word "view" used in naming for demo purposes only
CREATE OR REPLACE VIEW role_permission_view AS
SELECT r.dept_code, r.pcode, p.pcode_description, r.access_level
FROM user_role r, permission_code p
WHERE r.pcode = p.pcode;

desc role_permission_view

col data_type format a15

SELECT column_name, data_type, data_length
FROM user_tab_cols
WHERE table_name = 'ROLE_PERMISSION_VIEW';

col type format a30

SELECT column_name, nullable, data_type || '(' || data_length || ')' TYPE
FROM user_tab_cols
WHERE table_name = 'ROLE_PERMISSION_VIEW';

SELECT column_name, DECODE(nullable, 'N', 'NOT NULL', NULL) "Null?", data_type || '(' || data_length || ')' TYPE
FROM user_tab_cols
WHERE table_name = 'ROLE_PERMISSION_VIEW';

-- this will fail
INSERT INTO role_permission_view
(dept_code, pcode, pcode_description, access_level)
VALUES
('DAN', 'DM', 'Morgan', 'W');

-- this will fail too
UPDATE role_permission_view
SET access_level = 'W'
WHERE dept_code = 'SKA';

-- another relational view
CREATE OR REPLACE VIEW employee_permission_view AS
SELECT e.employee_no, e.first_name || ' ' || e.last_name NAME, e.dept_code, r.pcode, r.access_level DEFACCLVL, u.access_level, p.pcode_description
FROM employee e, user_role r, user_permission u, permission_code p
WHERE e.dept_code = r.dept_code
AND e.employee_no = u.employee_no
AND r.pcode = u.pcode
AND r.pcode = p.pcode
ORDER BY 1,3;

desc employee_permission_view

SELECT column_name, DECODE(nullable, 'N', 'NOT NULL', NULL) "Null?", data_type || '(' || data_length || ')' TYPE
FROM user_tab_cols
WHERE table_name = 'EMPLOYEE_PERMISSION_VIEW';

SELECT * FROM employee_permission_view;

-- this will fail too
DELETE FROM employee_permission_view
WHERE dept_code = 'LCR';

Demos

Instead Of Insert Trigger

CREATE OR REPLACE TRIGGER ioft_insert_role_perm
INSTEAD OF INSERT
ON role_permission_view
FOR EACH ROW
DECLARE
x INTEGER;
BEGIN
SELECT COUNT(*)
INTO x
FROM permission_code
WHERE pcode = :NEW.pcode;

IF x = 0 THEN
    INSERT INTO permission_code
    (pcode, pcode_description, mod_user_id, mod_user_date)
    VALUES
    (:NEW.pcode, 'New Code', USER, SYSDATE);
END IF;

SELECT COUNT(*)
INTO x
FROM dept_code
WHERE dept_code = :NEW.dept_code;

IF x = 0 THEN
    INSERT INTO dept_code
    (dept_code, dept_name)
    VALUES
    (:NEW.dept_code, 'New Dept');
END IF;

INSERT INTO user_role
(dept_code, pcode, mod_user_id)
VALUES
(:NEW.dept_code, :NEW.pcode, 'Morgan');

INSERT INTO test
(test)
VALUES
('Z');
END ioft_insert_role_perm;
/

SELECT *
FROM permission_code
WHERE pcode = 'DM';

SELECT *
FROM dept_code
WHERE dept_code = 'DAN';

SELECT *
FROM user_role
WHERE dept_code = 'DAN';

SELECT * FROM test;

-- insert works
INSERT INTO role_permission_view
(dept_code, pcode, pcode_description, access_level)
VALUES
('DAN', 'DM', 'Morgan', 'W');

-- view results
SELECT *
FROM permission_code
WHERE pcode = 'DM';

SELECT *
FROM dept_code
WHERE dept_code = 'DAN';

SELECT *
FROM user_role
WHERE dept_code = 'DAN';

SELECT * FROM test;

Instead Of Update Trigger

CREATE OR REPLACE TRIGGER ioft_role_perm
INSTEAD OF UPDATE
ON role_permission_view
FOR EACH ROW
BEGIN
UPDATE user_role
SET access_level = :NEW.access_level,
mod_user_id = USER,
mod_user_date = SYSDATE
WHERE dept_code = :OLD.dept_code
AND permission_code = :OLD.permission_code;
END ioft_role_perm;
/

SELECT trigger_name, trigger_type, action_type,
description
FROM user_triggers;

SELECT * FROM employee_permission_view;

UPDATE role_permission_view
SET access_level = 'W'
WHERE dept_code = 'SKA';

SELECT * FROM employee_permission_view;

UPDATE employee_permission
SET access_level = 'Z';

Instead Of Delete Trigger

/* what does it mean to delete LCR from employee_permission_view?
Does it mean delete the LCR department from the dept_code table?
Does it mean delete all employees that are in department LCR?
Does it mean set to null the dept_code for employees in department LCR? */

SELECT * FROM employee_permission_view;

SELECT * FROM dept_code;

SELECT * FROM employee;

-- let's delete the parent record and set the child to null and update two other columns
CREATE OR REPLACE TRIGGER ioft_emp_perm
INSTEAD OF DELETE
ON employee_permission_view
FOR EACH ROW
BEGIN
DELETE FROM dept_code
WHERE dept_code = :OLD.dept_code;

UPDATE employee
SET dept_code = NULL,
mod_user_id = USER,
mod_user_date = SYSDATE
WHERE dept_code = :OLD.dept_code;

DELETE FROM test
WHERE test = 'Z';
END ioft_emp_perm;
/

SELECT * FROM employee_permission_view;

DELETE FROM employee_permission_view
WHERE dept_code = 'LCR';

desc employee

DELETE FROM employee_permission_view
WHERE dept_code = 'LCR';

Instead-Of Trigger with Referencing Clause

Referencing Clause with Nested Tables

conn scott/tiger

CREATE OR REPLACE TYPE emp_type AS OBJECT (
empno    NUMBER(4),
ename    VARCHAR2(10),
job      VARCHAR2(9),
mgr      NUMBER(4),
hiredate DATE,
sal      NUMBER(7, 2),
comm     NUMBER(7, 2));
/

CREATE OR REPLACE TYPE emp_tab_type AS TABLE OF emp_type;
/

CREATE OR REPLACE TYPE dept_type AS OBJECT (
deptno NUMBER(2),
dname VARCHAR2(14),
loc    VARCHAR2(13),
emps   emp_tab_type);
/

CREATE OR REPLACE VIEW dept_or OF dept_type
WITH OBJECT IDENTIFIER (deptno) AS
SELECT deptno, dname, loc, CAST(MULTISET(
SELECT empno, ename, job, mgr, hiredate, sal, comm
FROM emp
WHERE emp.deptno = dept.deptno) AS emp_tab_type)
FROM dept;
/

CREATE OR REPLACE TRIGGER dept_emplist_tr
INSTEAD OF UPDATE ON NESTED TABLE emps OF dept_or
REFERENCING NEW AS NEW PARENT AS PARENT
FOR EACH ROW
BEGIN
dbms_output.put_line('New: ' || :NEW.job);
dbms_output.put_line('Parent: ' || :PARENT.dname);
END;
/

set serveroutput on

UPDATE TABLE (
SELECT p.emps
FROM dept_or p
WHERE deptno = 10)
SET ename = LOWER(ename);

Object-Relational View Instead Of Trigger

Object View Instead Of Trigger

-- for demo table and data: See Object-Relational Views link at page bottom

INSERT INTO ov_empdept
(empno, ename, dept)
VALUES
(4, 'D. Morgan', t_dept(7, 'MKT', 'Houston'));

CREATE OR REPLACE TRIGGER ioft_ov_empdept
INSTEAD OF INSERT
ON ov_empdept
FOR EACH ROW
BEGIN
INSERT INTO emp
(empno, ename, job, mgr, hiredate,
sal, comm, deptno)
VALUES
(:NEW.empno, :NEW.ename, :NEW.job, :NEW.mgr, :NEW.hiredate,
:NEW.sal, :NEW.comm, :NEW.dept.deptno);

INSERT INTO dept
(deptno, dname, location)
VALUES
(:NEW.dept.deptno, :NEW.dept.dname, :NEW.dept.location);
END ioft_ov_empdept;
/

INSERT INTO ov_empdept
(empno, ename, dept)
VALUES
(4, 'D. Morgan', t_dept(7, 'MKT', 'Houston'));

COMMIT;

SELECT * FROM ov_empdept;

SELECT * FROM emp;

SELECT * FROM dept;

-- demo corrected with the help of Kent Williamson. Thanks.