General | |||||
Data Dictionary Objects |
| ||||
System Privileges | create trigger create any trigger administer database trigger alter any trigger drop any trigger | ||||
Notes on Updatable Views
| |||||
| |||||
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', '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, 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. |
Oracle Instead-Of Triggers
最新推荐文章于 2023-05-16 23:38:37 发布