Oracle触发器5-Instead of触发器


Oracle触发器5-Instead of触发器
 
Oracle触发器4-数据库事件触发器
http://www.zzzyk.com/database/201304/201423.html
 
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