Oracle触发器5-Instead of触发器

Instead of 触发器可以在由一个以上的表组成的视图上进行insert,update,delete,merge.也可以使用instead of 来创建分区表等。

创建instead of 触发器语法：

1 CREATE [OR REPLACE] TRIGGER trigger_name
2 INTEAD OF operation
3 ON view_name
4 FOR EACH ROW
5 BEGIN
6 ...code goes here...
7 END;

下面开始测试 Instead of insert,instead of update,instead of delete触发器以及嵌套表的instead of 触发器

1.创建测试表employee,permission_code,user_role

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));

2.向表中插入测试数据：

-- 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;

3.创建视图role_permission_view和employee_permission_view并测试是否能更新、删除视图数据。

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;

-- 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;

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

4.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;

5.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';

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),
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);

--------------------------------------------------------------以上内容测试并整理自互联网----------------------------------------------------------------------------