A view presents the output of a query as a table. If you want to change a view as you would change a table, you must create INSTEAD OFtriggers. Instead of changing the view, they change the underlying tables.
For example, consider the view EMP_LOCATIONS, whose NAME column is created from the LAST_NAME and FIRST_NAME columns of theEMPLOYEES table:
CREATE VIEW EMP_LOCATIONS AS SELECT e.EMPLOYEE_ID, e.LAST_NAME || ', ' || e.FIRST_NAME NAME, d.DEPARTMENT_NAME DEPARTMENT, l.CITY CITY, c.COUNTRY_NAME COUNTRY FROM EMPLOYEES e, DEPARTMENTS d, LOCATIONS l, COUNTRIES c WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID AND d.LOCATION_ID = l.LOCATION_ID AND l.COUNTRY_ID = c.COUNTRY_ID ORDER BY LAST_NAME;
To update EMP_LOCATIONS.NAME, you must update EMPLOYEES.LAST_NAME and EMPLOYEES.FIRST_NAME. This is what the INSTEAD OF trigger inExample 8-1 does.
This trigger is part of the sample application that the tutorials and examples in this document show how to develop and deploy.
NEW and OLD are pseudorecords that the PL/SQL run-time engine creates and populates whenever a row-level trigger fires. OLD and NEWstore the original and new values, respectively, of the record being processed by the trigger. They are called pseudorecords because they do not have all properties of PL/SQL records.
Example 8-1 Creating an INSTEAD OF Trigger
CREATE OR REPLACE TRIGGER update_name_view_trigger INSTEAD OF UPDATE ON emp_locations BEGIN UPDATE employees SET first_name = substr( :NEW.name, instr( :new.name, ',' )+2), last_name = substr( :NEW.name, 1, instr( :new.name, ',')-1) WHERE employee_id = :OLD.employee_id;
------------------这两天就遇到一个问题，有两张表的一个字段需要进行同步更新，也就是A表修改时要把对应的B表的记录 字段修改，反过来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>
----------------------------------------
扫一扫
5674
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
