Oracle触发器5-Instead of触发器(二)
, 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 employ