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

--------------------------------------------------------------以上內容測試並整理自互連網----------------------------------------------------------------------------