instead of触发器实现复杂视图dml和应用逻辑

对于简单视图，可以直接执行INSERT，UPDATE和DELETE操作
但是对于复杂视图，不允许直接执行INSERT，UPDATE和DELETE操作。
为了在具有以上情况的复杂视图上执行DML操作需要征用触发器来完成
当视图中包含以下结构之一，就表示为不可更新的视图,都不允许直接执行DML操作
1）具有集合操作符（UNION，UNION ALL，INTERSECT，MINUS）；
2）具有分组函数（MIN,MAX,SUM,AVG,COUNT等）统计函数；
3）具有GROUP BY，CONNECT BY或START WITH等子句,HAVING 子句；
4）具有DISTINCT关键字；
5）具有连接查询(集合运算连接)
6)CASE 或者DECODE 语句
替代触发器创建时不需要使用BEFORE或者AFTER，而将其替换为INSTEAD OF，同时操作的对象也有表替换为视图

--创建复杂视图
CREATE OR REPLACE VIEW v_emp20
AS
SELECT e.empno,e.ename,e.job,e.sal,d.deptno,d.dname,d.loc
FROM emp e,dept d
WHERE e.deptno=d.deptno;
--查看视图
SELECT * FROM user_views;

--INSTEAD OF触发器可以实现更新视图时多个数据表一起更新的问题
--instead-of触发器创建语法
CREATE [OR REPLACE] TRIGGER 触发器名称
INSTEAD OF [INSERT | UPDATE | UPDATE OF 列名称 [,列名称,...] | DELETE] ON 视图名称
[FOR EACH ROW]
[WHEN 触发条件]
[DECLARE]
[程序声明部分 ;]
BEGIN
程序代码部分 ;
END [触发器名称] ;

--case 1、创建一个insert替代触发器用于执行图添加操作
create or replace trigger view_insert_tigger
instead of insert on v_emp20
for each row
declare
v_empCount NUMBER;
v_deptCount NUMBER;
begin
--判断要增加的员工是否存在
SELECT COUNT(empno) INTO v_empCount FROM emp WHERE empno=:NEW.empno;
--判断要部门是否存在
SELECT COUNT(deptno) INTO v_deptCount FROM dept WHERE deptno=:new.deptno;
--如果员工不存在
IF v_empCount=0 THEN
INSERT INTO emp(empno,ename,job,sal,deptno)
VALUES(:new.empno,:new.ename,:new.job,:new.sal,:new.deptno);
END IF;
--如果部门不存在
IF v_deptCount=0 THEN
INSERT INTO dept(deptno,dname,loc)VALUES(:new.deptno,:new.dname,:new.loc);
END IF;
end view_insert_tigger;

--添加数据
INSERT INTO v_emp20(empno,ename,job,sal,deptno,dname,loc)
VALUES(7777,'张三丰','CLERK',800,77,'活动部','深圳');

--case 2、创建一个update替代触发器用于执行视图更新操作
create or replace trigger view_update_tigger
INSTEAD OF update on v_emp20
for each row
declare
begin
UPDATE emp SET ename=:new.ename,job=:new.job,sal=:new.sal WHERE empno=:NEW.empno;
UPDATE dept SET dname=:new.dname,loc=:new.loc WHERE deptno=:new.deptno;
end view_update_tigger;

UPDATE v_emp20 SET ename='任我行',sal=2000,dname='魔教' WHERE empno=7777;
COMMIT;
--查询
SELECT * FROM v_emp20;

--case 3、创建一个DELETE替代触发器用于执行视图的删除操作
create or replace trigger view_delete_tigger
instead of delete on v_emp20
for each row
declare
v_empCount NUMBER;
BEGIN
--判断员工是否存在
SELECT COUNT(empno) INTO v_empCount FROM emp WHERE empno=:old.empno;
--如果员工存在
IF v_empCount>0 THEN
DELETE FROM emp WHERE empno=:old.empno;
END IF;
end view_delete_tigger;

--执行删除
DELETE FROM v_emp20 WHERE empno=7777;
COMMIT;
--查询
SELECT * FROM v_emp20;
SELECT * FROM emp;

--case 4、将以上三个合为一个
create or replace trigger view20emp_trigger
instead of INSERT OR UPDATE OR DELETE on v_emp20
for each row
declare
v_empCount NUMBER;
v_deptCount NUMBER;
BEGIN
IF inserting THEN
--判断要增加的员工是否存在
SELECT COUNT(empno) INTO v_empCount FROM emp WHERE empno=:NEW.empno;
--判断要增加的部门是否存在
SELECT COUNT(deptno) INTO v_deptCount FROM dept WHERE deptno=:new.deptno;
--员工不存在就增加
IF v_empCount=0 THEN
INSERT INTO emp(empno,ename,job,sal,deptno)
VALUES(:new.empno,:new.ename,:new.job,:new.sal,:new.deptno);
END IF;
--如果部门不存在
IF v_deptCount=0 THEN
INSERT INTO dept(deptno,dname,loc)VALUES(:new.deptno,:new.dname,:new.loc);
END IF;
ELSIF updating THEN
UPDATE emp SET ename=:new.ename,job=:new.job,sal=:new.sal WHERE empno=:NEW.empno;
UPDATE dept SET dname=:new.dname,loc=:new.loc WHERE deptno=:new.deptno;
ELSIF deleting THEN
--判断员工是否存在
SELECT COUNT(empno) INTO v_empCount FROM emp WHERE empno=:old.empno;
--如果员工存在
IF v_empCount>0 THEN
DELETE FROM emp WHERE empno=:old.empno;
END IF;
ELSE
NULL;
END IF;
end view20emp_trigger;

--执行增加、修改、删除
--添加数据
INSERT INTO v_emp20(empno,ename,job,sal,deptno,dname,loc)
VALUES(7777,'张三丰','CLERK',800,77,'活动部','深圳');
--查询
SELECT * FROM v_emp20;
UPDATE v_emp20 SET ename='任我行',sal=2000,dname='魔教' WHERE empno=7777;
COMMIT;
--查询
SELECT * FROM v_emp20;
--执行删除
DELETE FROM v_emp20 WHERE empno=7777;
COMMIT;
--查询
SELECT * FROM v_emp20;
SELECT * FROM emp;
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
开发中遇到一个情况：前台页面是一个角色对用户的批量授权，其中用户是多选，多选的结果是控件里会填入逗号分隔的用户ID字符串，比如“TOM,JERRY”，最终要分别插入2张一对多的主从表中。
风险：业务逻辑被触发器的代码轻易改变了。
假设前台用户选择的是DBA权限，而在触发器中完全可以被篡改成SYSDBA权限，或者执行一堆其他的操作。很容易引起前台人员和后台人员对于业务逻辑实现的混乱。
--主表
CREATE TABLE T_MAIN
(GUID VARCHAR2(32) primary key
,GRANTOR VARCHAR2(30)
,ROLEID VARCHAR2(10)
,CREATED DATE
);
--从表
CREATE TABLE T_DETAIL
(GUID VARCHAR2(32)
,GRANTEE VARCHAR2(32)
,ROLEID VARCHAR2(10)
);
--前台展示的的是一个视图：
CREATE OR REPLACE VIEW V_MAIN_DETAIL AS
SELECT A.GUID,A.GRANTOR,A.ROLEID,A.CREATED,TO_CHAR(WM_CONCAT(B.GRANTEE)) GRANTEES
FROM T_MAIN A,T_DETAIL B
WHERE A.GUID=B.GUID
GROUP BY A.GUID,A.GRANTOR,A.ROLEID,A.CREATED;

前台实现的做法是在主表中插入数据，然后对用户多选结果进行循环插入从表。
Oracle里的视图是不能做DML操作的，尽快Oracle做了很多改进，比如对单表视图或一对一键值关联的多表视图可以做增删改。但一对多或者多对多关联的视图还是不支持增删改操作，会报错ORA-01779: cannot modify a column which maps to a non key-preserved table

--测试聚合列裂变为对应的多行 by zhuyj
create table zyj.t_GRANTEE(GRANTEE varchar2(2000));

declare
v_GRANTEE varchar2(2000);
v_deli varchar2(20);
GRANTEE varchar2(2000);
begin
v_GRANTEE:='tom,jerry';
v_deli:=',';
delete from zyj.t_GRANTEE;
insert into zyj.t_grantee
select substr(t.ca,instr(t.ca,v_deli,1,c.lv)+1,instr(t.ca,v_deli,1,c.lv+1)-(instr(t.ca,v_deli,1,c.lv)+1)) as GRANTEE
from (select v_deli||v_GRANTEE||v_deli as ca,length(v_deli||v_GRANTEE||v_deli)-nvl(length(replace(v_deli||v_GRANTEE||v_deli,v_deli)),0)-1 as cnt from dual) t,
(select level lv from dual connect by level<=200) c
where c.lv<=t.cnt;
commit;
end;

--case 1:insert trigger
CREATE OR REPLACE TRIGGER TRI_V_MAIN_DETAIL
INSTEAD OF INSERT
ON V_MAIN_DETAIL
DECLARE
--分解用分隔符分隔的字符串
cursor cur_GRANTEE(v_GRANTEE varchar2,v_deli varchar2) is
select substr(t.ca,instr(t.ca,v_deli,1,c.lv)+1,instr(t.ca,v_deli,1,c.lv+1)-(instr(t.ca,v_deli,1,c.lv)+1)) as GRANTEE
from (select v_deli||v_GRANTEE||v_deli as ca,length(v_deli||v_GRANTEE||v_deli)-nvl(length(replace(v_deli||v_GRANTEE||v_deli,v_deli)),0)-1 as cnt from dual) t,
(select level lv from dual connect by level<=200) c
where c.lv<=t.cnt;
v_guid varchar2(32);
BEGIN
if :new.GRANTEES is null then
raise_application_error(-20000,'没有选择被授权用户！');
else
select sys_guid() into v_guid
from dual;
--插入主表数据1条
insert into T_MAIN(GUID,
GRANTOR,
ROLEID,
CREATED)
values(v_guid,:new.grantor,:new.roleid,sysdate);
--插入从表数据N条
for rec_GRANTEE in cur_GRANTEE(:new.GRANTEES,',') loop
insert into t_detail(guid,
grantee,
roleid)
values(v_guid,rec_grantee.GRANTEE,:new.roleid);
end loop;
end if;
end;

INSERT INTO V_MAIN_DETAIL(GRANTOR,ROLEID,GRANTEE) VALUES('dongfeng','dba','jack','rose');
SELECT * FROM V_MAIN_DETAIL;
SELECT * FROM T_MAIN;
SELECT * FROM T_DETAIL;

--case 2:update trigger
CREATE OR REPLACE TRIGGER TRI_V_MAIN_DETAIL_UPD
INSTEAD OF UPDATE
ON V_MAIN_DETAIL
DECLARE
--分解用分隔符分隔的字符串
cursor cur_GRANTEE(v_GRANTEE varchar2,v_deli varchar2) is
select substr(t.ca,instr(t.ca,v_deli,1,c.lv)+1,instr(t.ca,v_deli,1,c.lv+1)-(instr(t.ca,v_deli,1,c.lv)+1)) as GRANTEE
from (select v_deli||v_GRANTEE||v_deli as ca,length(v_deli||v_GRANTEE||v_deli)-nvl(length(replace(v_deli||v_GRANTEE||v_deli,v_deli)),0)-1 as cnt from dual) t,
(select level lv from dual connect by level<=200) c
where c.lv<=t.cnt;
BEGIN
--更新主表数据
UPDATE T_MAIN T SET
GRANTOR=:NEW.GRANTOR,ROLEID=:NEW.ROLEID,CREATED=SYSDATE
WHERE GUID=:NEW.GUID;
if :new.GRANTEES!=:OLD.GRANTEES then
--重新插入从表数据N条
delete from t_detail where guid=:new.GUID;
for rec_GRANTEE in cur_GRANTEE(:new.GRANTEES,',') loop
insert into t_detail(guid,
grantee,
roleid)
values(v_guid,rec_grantee.GRANTEE,:new.roleid);
end loop;
end if;
end;

UPDATE V_MAIN_DETAIL T SET T.ROLEID='SYSDBA',T.GRANTEE='Tom,Jerry' WHERE GUID='';
SELECT * FROM V_MAIN_DETAIL;
SELECT * FROM T_MAIN;
SELECT * FROM T_DETAIL;

--case 3:delete trigger
CREATE OR REPLACE TRIGGER TRI_V_MAIN_DETAIL_DEL
INSTEAD OF DELETE
ON V_MAIN_DETAIL
DECLARE
BEGIN
--删除从表数据
DELETE FROM T_DETAIL T WHERE GUID=:OLD.GUID;
--删除主表数据
DELETE FROM T_MAIN T WHERE GUID=:OLD.GUID;
end;

DELETE FROM V_MAIN_DETAIL T WHERE GUID='';
SELECT * FROM V_MAIN_DETAIL;
SELECT * FROM T_MAIN;
SELECT * FROM T_DETAIL;