Oracle触发器5-Instead of触发器(三)
ee_permission
SET access_level = 'Z';
6.Instead Of Delete Trigger 举例：
SELECT * FROM employee_permission_view;
SELECT * FROM dept_code;
SELECT * FROM employee;
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';
7.嵌套表的instead of 触发器举例：
conn scott/tiger
CREATE OR REPLACE TYPE emp_type AS OBJECT (
empno NUMBER(4),
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
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);