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 |
|
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 (
permission_code
VARCHAR2(2),
permission_code_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),
permission_code 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),
permission_code 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', 'Cline', 'Jack', '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, permission_code, access_level)
SELECT r.dept_code, p.permission_code, 'R'
FROM dept_code r, permission_code p;
INSERT INTO user_permission
(employee_no, permission_code, access_level)
SELECT e.employee_no, r.permission_code, r.access_level
FROM employee e, user_role r
WHERE e.dept_code = r.dept_code;
COMMIT; |
|
Relational Views Instead Of Trigger |
Instead Of Trigger Demo Views |
CREATE OR REPLACE VIEW
role_permission AS
SELECT r.dept_code, r.permission_code, p.permission_code_description, r.access_level
FROM user_role r, permission_code p
WHERE r.permission_code = p.permission_code;
desc role_permission
col data_type format a15
SELECT column_name, data_type, data_length
FROM user_tab_cols
WHERE table_name = 'ROLE_PERMISSION';
col type format a30
SELECT column_name, nullable,
data_type || '(' || data_length || ')' TYPE
FROM user_tab_cols
WHERE table_name = 'ROLE_PERMISSION';
SELECT column_name, DECODE(nullable, 'N', 'NOT
NULL', NULL) "Null?",
data_type || '(' || data_length || ')' TYPE
FROM user_tab_cols
WHERE table_name = 'ROLE_PERMISSION';
CREATE OR REPLACE VIEW employee_permission AS
SELECT e.employee_no,
e.first_name || ' ' || e.last_name NAME, e.dept_code,
r.permission_code, r.access_level DEFACCLVL, u.access_level,
p.permission_code_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.permission_code = u.permission_code
AND r.permission_code = p.permission_code;
desc employee_permission
SELECT column_name, DECODE(nullable, 'N', 'NOT
NULL', NULL) "Null?",
data_type || '(' || data_length || ')' TYPE
FROM user_tab_cols
WHERE table_name = 'EMPLOYEE_PERMISSION'; |
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); |
Instead-Of Trigger Demo |
| Demo DML Statements |
INSERT INTO role_permission
(dept_code, permission_code,
permission_code_description, access_level)
VALUES
('DAN', 'DM', 'Morgan', 'W');
UPDATE role_permission
SET access_level = 'W'
WHERE dept_code = 'SKA'; |
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.
|
Instead Of Insert Trigger Demo |
CREATE OR REPLACE TRIGGER
ioft_insert_role_perm
INSTEAD OF INSERT
ON role_permission
FOR EACH ROW
BEGIN
INSERT INTO user_role
(dept_code, permission_code, mod_user_id)
VALUES
('XXX', 'XX', 'Me');
INSERT INTO test
(test)
VALUES
('Z');
END ioft_insert_role_perm;
/
INSERT INTO role_permission
(dept_code, permission_code,
permission_code_description, access_level)
VALUES
('DAN', 'DM', 'Morgan', 'W');
|
Instead Of update Trigger Demo |
CREATE OR REPLACE TRIGGER ioft_role_perm
INSTEAD OF UPDATE
ON role_permission
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;
CREATE OR REPLACE TRIGGER ioft_emp_perm
INSTEAD OF UPDATE
ON employee_permission
FOR EACH ROW
BEGIN
UPDATE user_permission
SET access_level = :NEW.access_level,
mod_user_id = USER,
mod_user_date = SYSDATE
WHERE employee_no = :OLD.employee_no
AND permission_code = :OLD.permission_code;
DELETE FROM test
WHERE test = 'Z';
END ioft_emp_perm;
/
UPDATE employee_permission
SET employee_no = 66;
SELECT * FROM employee_permission;
UPDATE employee_permission
SET access_level = 'Z';
SELECT * FROM employee_permission; |
|
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;
-- example corrected with the help of Kent Williamson. Thanks. |
|