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);
--------------------------------------------------------------以上内容测试并整理自互联网----------------------------------------------------------------------------
浙公网安备 33010602011771号