General |
| Data Dictionary Objects |
| trigger$
|
dba_triggers |
all_triggers |
user_triggers |
|
| System Privileges |
create trigger
create any trigger
administer database trigger
alter any trigger
drop any trigger |
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:
- A set operator
- A DISTINCT operator
- An aggregate or analytic function
- A GROUP BY, ORDER BY, MODEL, CONNECT BY, or START WITH clause
- A collection expression in a SELECT list
- A subquery in a SELECT list
- A subquery designated WITH READ ONLY
- 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.
|
|
Demo Tables |
Instead Of Demo Tables And Constraints |
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)); |
Instead Of Trigger
Demo Data |
-- employee table
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');
-- permission_code table data
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 |
Build these views for later use in demonstrating Instead-Of Triggers |
-- 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'; |
Instead Of Insert Trigger Demo |
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 Demo |
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 Demo |
/* 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 |
-- demo table and data: See
Object-Relational Views
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;
-- demo corrected with the help of Kent Williamson. Thanks. |
|
