想到一个办法绕过触发器的问题,就是再建一个log表,来存储修改的ID,U_SMRYGL表的触发器只是负责把数据的关键字插入log表。
然后再log表上再建个insert的触发器,由这个触发器来完成U_SMRYGL的数据更新。
create or replace trigger tg_APP_del_U_SMRYGL
AFTER delete on U_SMRYGL
for each row
declare
MY_N_ORDER NUMBER;
MY_N_ID NUMBER;
my_n_num NUMBER;
MY_N_MIN_ORDER NUMBER;
MY_N_MIN_ID NUMBER;
my_n_min_num NUMBER;
begin
insert into pp_log(userid,tablename) values(:OLD.USERID,'U_SMRYGL');
end tg_APP_del_U_SMRYGL;
create table pp_log
(userid varchar2(30),
tablename varchar(200)
)
create or replace trigger tg_APP_pp_log
before insert on pp_log
for each row
declare
MY_N_ORDER NUMBER;
MY_N_ID NUMBER;
my_n_num NUMBER;
MY_N_MIN_ORDER NUMBER;
MY_N_MIN_ID NUMBER;
my_n_min_num NUMBER;
begin
SELECT MAX(NVL(ORDERNO,0)) INTO MY_N_ORDER FROM U_SMRYGL WHERE USERID= :NEW.USERID ;
SELECT MAX(ID) INTO MY_N_ID FROM U_SMRYGL WHERE USERID= :NEW.USERID AND ORDERNO = MY_N_ORDER;
UPDATE T_EMPLOYEE SET (SHEMIEND) = (SELECT ENDDATE FROM U_SMRYGL WHERE ID=MY_N_ID)
WHERE USERID=:OLD.USERID;
end tg_APP_pp_log;
结果就是我的小伎俩没有骗过Oracle,
从后台的出错信息中可以看出,这个调用关系是:
1先调用U_SMRYGL的触发器
2调用log表的触发器
3返回调用U_SMRYGL的触发器(就是这个返回,我就这么死了。这么一返回,我就违反规则了
)