Oracle触发器5(INSTEAD OF触发器)
前提：对于简单的视图，可以直接进行DML操作，但是对于复杂视图，不允许直接执行DML操作，当视图符合以下任何一种情况都不可以：
具有集合操作符(UNION,UNION ALL,INTERSECT,MINUS)；
具有分组函数(MIN,MAX,SUM,AVG,COUNT)；
具有GROUP BY,CONNECT BY 或START WITH子句；
具有DISTINCT关键字
具有连接查询
在具有以上情况的复杂视图执行DML操作，必须要基于视图建立INSTEAD OF触发器。
建立之后，就可以基于复杂视图执行DML语句
注意事项：
INSTEAD OF选项只适用于视图
当基于视图建立触发器时，不能指定BEFORE和AFTER选项
在建立视图时没有指定WITH CHECK OPTION选项
当建立INSTEAD OF触发器时，必须指定FOR ECH ROW选项
——又小又大型的例子：
1.建立复杂视图dept_emp
视图时逻辑表，本身没有任何数据。
视图只是对应一条SELECT语句。
当查询视图时，其数据实际是从视图基表上取得。
为了简化部门及其雇员信息的查询，应建立复杂视图dept_emp
CREATE OR REPLACE VIEW dept_emp AS
SELECT a.deptno,a.dname,b.empno,b.ename
FROM dept a,emp b
WHERE a.deptno=b.deptno;
当执行以上语句之后，直接查询视图会显示相关信息，但不允许DML操作。
2.建立INSTEAD OF触发器
为了可以在复杂视图上执行DML操作，必须要基于复杂视图来建立INSTEAD OF触发器。
下面以复杂视图dept_emp上执行INSERT 操作为例：
CREATE OR REPLACE TRIGGER tr_instead_of_dept_emp
INSTEAD OF INSERT ON dept_emp
FOR EACH ROW
DELARE
v_temp INT;
BEGIN
SELECT count(*) INTO v_temp FROM dept
WHERE deptno=：new.deptno;
IF v_temp=0 THEN
INSERT INTO dept(deptno,dname) VALUES(:new.deptno,:new.dname);
END IF;
SELECT count(*) INTO v_temp FROM emp
WHERE empno=:new.empno;
IF v_temp=0 THEN
今天在论坛上遇到个触发器的问题,需求如下:
向一个表中插入一条新记录 如(2007 ，a)
触发器 查询该表中如果有(2007 ，a)这条数据就更新这条数据中的另一个字段 如果没有就插入一条新记录触发器
if（如果存在）
更新记录
else
插入新记录
由于本人也不经常写复杂的触发器,经过一番尝试终于得出结论.如果想要中止触发动作在oracle里面似乎只能抛出异常,否则是无法实现的,只有使用Instead of 才行,于是建立视图
create or replace view vw_ayear as
select "YR","STARTFLAG","STARTDATE","CLOSETAG","TURNFLAG" from ayear
然后在写触发器
CREATE OR REPLACE TRIGGER ayear_TR
INSTEAD OF INSERT
ON vw_ayear
FOR EACH ROW
DECLARE TEMP INT ;
BEGIN
select 0 into TEMP from dual;
select count(*) into TEMP from ayear a where a.yr=:new.yr and a.startflag=:new.startflag;
IF TEMP <> 0 THEN
UPDATE ayear a SET a.startdate = :new.startdate , a.closetag=:new.closetag , a.turnflag=:new.turnflag
where a.yr=:new.yr and a.startflag=:new.startflag;
ELSE
insert into ayear values(:new.yr,:new.startflag,:new.startdate,:new.closetag,:new.turnflag);
END IF;
END;
最后测试
insert into vw_ayear values('2008','1',to_date('2007-04-01','yyyy-MM-dd'),'0','0')
触发器里面不能有commit,rollback的操作
无法操作触发器所在的这个表，不管是before 还是after
唯一的方法就是在不满足条件时人工抛出一个错误
还有就是使用视图了,呵呵,不知道说的对不对如果错了还请高人赐教.
字段修改，反过来B表修改时也要把A表的修改，保持两边数据的一个同步，这个可以在前台很容易的实现，但开发
人员不想修改代码了，就考虑在后台用trigger实现。
功能很简单，但在实现时遇到一个问题，就是A上的DML触发了上面的TRIGGER，然后这个TRIGGER去更新B表，这样
就会触发B表上的触发器，而B表上的TRIGGER又会更新A表，这样就迭代触发，没有结束了，也就是会产生变异表（mutating）
我不知道ORACLE的触发器是否有属性来限制这种情况的发生，但以前做SQL SERVER时知道有种Instaed of的触发器，他表示
当DML启动他后，他将以TRIGGER里的代码来代替这个DML动作，也就是DML不会真正的执行，只会启动INSTEAD TRIGGER，最终
执行的是TRIGGER里面的编码。
查看了Docs，看到ORACLE也支持这个类型的触发器，但这个只能建立到视图上，不能基于表建立，我要的功能肯定是可以
实现的，在这里我把原表进行了rename，引如了两张视图，名字就是以前的表名，这样对于他们前台应用就做了个
透明的切换，然后在两个视图上建立INSTEAD触发器，将任何两个视图上的更新都传播到后面的两个基表，这样不管你更新那个
视图，我都可以捕获到数据，以代码在后面更新，也不存在互相触发，因为触发器修改的对象已经转移到表了，而此时表上是没有
trigger的，呵呵！！！
过程如下
--创建测试表
SQL> create table mytest1(row_num number,row_name varchar2(50));
表被创建
SQL> create table mytest2(row_num number,row_name varchar2(50));
表被创建
--测试数据
SQL> INSERT INTO MYTEST1 VALUES(1,'Fuck!!!');
1 行 已插入
SQL> INSERT INTO MYTEST2 VALUES(1,'Watch your mouth!!!');
1 行 已插入
SQL> COMMIT;
提交完成
--先在一个表上创建触发器
SQL> CREATE OR REPLACE TRIGGER TRI_TEST1
2 BEFORE UPDATE
3 ON MYTEST1
4 FOR EACH ROW
5 DECLARE
6 lv_new VARCHAR2(20);
7 lv_parent VARCHAR2(20);
8 BEGIN
9 lv_new := :new.row_name;
10 lv_parent := :OLD.row_name;
11 IF lv_new <> lv_parent THEN
12 UPDATE MYTEST2
13 SET ROW_NAME = :NEW.ROW_NAME
14 WHERE ROW_NUM = :NEW.ROW_NUM;
15 END IF;
16 DBMS_OUTPUT.PUT_LINE(lv_new || lv_parent);
17 END;
18 /
触发器被创建
--测试更新
SQL> set serveroutput on
SQL> UPDATE MYTEST1 SET ROW_NAME = 'DO it!!!';
DO it!!! Fuck!!!
1 行 已更新
--更新成功
SQL> SELECT * FROM MYTEST2;
ROW_NUM ROW_NAME
---------- --------------------------------------------------
1 DO it!!!
--另外张表创建触发器
SQL> CREATE OR REPLACE TRIGGER TRI_TEST2
2 BEFORE UPDATE
3 ON MYTEST2
4 FOR EACH ROW
5 DECLARE
6 lv_new VARCHAR2(20);
7 lv_parent VARCHAR2(20);
8 BEGIN
9 lv_new := :new.row_name;
10 lv_parent := :OLD.row_name;
11 IF lv_new <> lv_parent THEN
12 UPDATE MYTEST1
13 SET ROW_NAME = :NEW.ROW_NAME
14 WHERE ROW_NUM = :NEW.ROW_NUM;
15 END IF;
16 DBMS_OUTPUT.PUT_LINE(lv_new || lv_parent);
17 END;
18 /
--产生了变异表，更新失败
SQL> update mytest1 set row_name = 'mouthkkkkkoo';
update mytest1 set row_name = 'mouthkkkkkoo'
ORA-04091: table MYTEST1 is mutating, trigger/function may not see it
ORA-06512: at "TRI_TEST2", line 8
ORA-04088: error during execution of trigger 'TRI_TEST2'
ORA-06512: at "TRI_TEST1", line 8
ORA-04088: error during execution of trigger 'TRI_TEST1'
--更新失败
SQL> update mytest2 set row_name = 'mouthkkkkkoo';
update mytest2 set row_name = 'mouthkkkkkoo'
ORA-04091: table MYTEST2 is mutating, trigger/function may not see it
ORA-06512: at "TRI_TEST1", line 8
ORA-04088: error during execution of trigger 'TRI_TEST1'
ORA-06512: at "TRI_TEST2", line 8
ORA-04088: error during execution of trigger 'TRI_TEST2'
--删除触发器
SQL> drop trigger TRI_TEST2;
触发器被删掉
SQL> drop trigger TRI_TEST1;
触发器被删掉
--创建视图
SQL> CREATE VIEW V_TEST1 AS SELECT * FROM MYTEST1;
视图被创建
SQL> CREATE VIEW V_TEST2 AS SELECT * FROM MYTEST2;
视图被创建
--基于视图创建Instead触发器
SQL> CREATE OR REPLACE TRIGGER TRI_TEST1
2 INSTEAD OF UPDATE
3 ON V_TEST1
4 FOR EACH ROW
5 DECLARE
6 lv_new VARCHAR2(20);
7 lv_parent VARCHAR2(20);
8 BEGIN
9 lv_new := :new.row_name;
10 lv_parent := :OLD.row_name;
11 IF lv_new <> lv_parent THEN
12 UPDATE MYTEST2
13 SET ROW_NAME = :NEW.ROW_NAME
14 WHERE ROW_NUM = :NEW.ROW_NUM;
15 UPDATE MYTEST1
16 SET ROW_NAME = :NEW.ROW_NAME
17 WHERE ROW_NUM = :NEW.ROW_NUM;
18 END IF;
19 DBMS_OUTPUT.PUT_LINE(lv_new || lv_parent);
20 END;
21 /
触发器被创建
SQL> CREATE OR REPLACE TRIGGER TRI_TEST2
2 INSTEAD OF UPDATE
3 ON V_TEST2
4 FOR EACH ROW
5 DECLARE
6 lv_new VARCHAR2(20);
7 lv_parent VARCHAR2(20);
8 BEGIN
9 lv_new := :new.row_name;
10 lv_parent := :OLD.row_name;
11 IF lv_new <> lv_parent THEN
12 UPDATE MYTEST2
13 SET ROW_NAME = :NEW.ROW_NAME
14 WHERE ROW_NUM = :NEW.ROW_NUM;
15 UPDATE MYTEST1
16 SET ROW_NAME = :NEW.ROW_NAME
17 WHERE ROW_NUM = :NEW.ROW_NUM;
18 END IF;
19 DBMS_OUTPUT.PUT_LINE(lv_new || lv_parent);
20 END;
21 /
触发器被创建
--功能已经实现
SQL> update v_test1 set row_name = 'I rock with you!!!';
1 行 已更新
SQL> commit;
提交完成
SQL> select * from v_test2;
ROW_NUM ROW_NAME
---------- --------------------------------------------------
1 I rock with you!!!
SQL> update v_test2 set row_name = 'Don''t kick me!!!';
1 行 已更新
SQL> commit;
提交完成
SQL> select * from v_test1;
ROW_NUM ROW_NAME
---------- --------------------------------------------------
1 Don't kick me!!!
SQL>