触发器调用带有commit的存储过程

[Melissa1986]

大家好！
今天遇到一个问题，很烦解决不掉！
我谢了一个触发器：
CREATE OR REPLACE TRIGGER createmyoprteq
AFTER INSERT OR UPDATE OR DELETE ON QIXIANG.OPRT_EQ
BEGIN
proc('oprtEq','oprt_psn_division','oprt_eq_model','oprt_eq_count','myoprteq');
END;

在这个触发器中调用一个存储过程：
create or replace procedure proc(tabname in varchar2,
col1 in varchar2,
col2 in varchar2,
col3 in varchar2,
viewname in varchar2 default 'v_tmp')
as
sqlstr varchar2(2000):='create or replace view '||viewname||' as select '||col1||' ';
c1 sys_refcursor;
v1 varchar2(100);
begin
open c1 for 'select distinct to_char('||col2||') from '||tabname;
loop
fetch c1 into v1;
exit when c1%notfound;
sqlstr:=sqlstr||'
,max(decode('||col2||','''||v1||''','||col3||'))"'||v1||'"';
end loop;
close c1;
sqlstr:=sqlstr||' from '||tabname||' group by '||col1;
execute immediate sqlstr;
end proc;

这个存储过程是把我的一个表进行行列转换，生成一个视图！因为触发器里面不能有commit关键字，所有不能执行！
不知道有什么办法可以解决！从网上查到可以使用自治事务：我把存储过程改成如下：

create or replace procedure proc(tabname in varchar2,
col1 in varchar2,
col2 in varchar2,
col3 in varchar2,
viewname in varchar2 default 'v_tmp')
as
PRAGMA AUTONOMOUS_TRANSACTION; //这是我加的部分！
sqlstr varchar2(2000):='create or replace view '||viewname||' as select '||col1||' ';
c1 sys_refcursor;
v1 varchar2(100);

begin
open c1 for 'select distinct to_char('||col2||') from '||tabname;
loop
fetch c1 into v1;
exit when c1%notfound;
sqlstr:=sqlstr||'
,max(decode('||col2||','''||v1||''','||col3||'))"'||v1||'"';
end loop;
close c1;
sqlstr:=sqlstr||' from '||tabname||' group by '||col1;
execute immediate sqlstr;
commit;
end proc;

执行没有错误了！可是好像并不能重新生成我的视图！但是我手动执行存储过程可以生成我需要的视图。
求高手帮忙呀！

[shiyiwan]

在这个procedure中sqlstr查询了触发器触发的表OPRT_EQ

[小灰狼W]

CREATE OR REPLACE TRIGGER createmyoprteq
AFTER INSERT OR UPDATE OR DELETE ON QIXIANG.OPRT_EQ
BEGIN
proc('oprtEq','oprt_psn_division','oprt_eq_model','oprt_eq_count','myoprteq');
commit;
END;
好高兴，我的过程派上用场了～

[小灰狼W]

看错了
是trigger里加自治事务
CREATE OR REPLACE TRIGGER createmyoprteq
AFTER INSERT OR UPDATE OR DELETE ON QIXIANG.OPRT_EQ
declare
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
proc('oprtEq','oprt_psn_division','oprt_eq_model','oprt_eq_count','myoprteq');
commit;
END;

过程不要改

[Melissa1986]

昨天直接用了，忘记谢谢了！
谢谢2楼！
你存储过程解决了我两天没有解决的问题！不太会写存储过程！
因为触发器里面不允许使用commit，所以不知道怎么调用这个存储过程！

[小灰狼W]

你把存储过程里加的那句PRAGMA AUTONOMOUS_TRANSACTION;去掉
在触发器里面加
就像我在3楼里写的那样，触发器的end前别忘了commit
再试试

[Melissa1986]

好的！太谢谢你，我试试！

[Melissa1986]

我试了你的方法！好像还是不行！执行的时候没有错误！可是视图没有被刷新，还是上次上次生成的视图！
很奇怪，直接使用存储过程的时候就好好的，通过触发器就不好使了，很郁闷！

[小灰狼W]

不会吧，重新编译了吗

create or replace procedure proc(tabname in varchar2,
                                  col1 in varchar2,
                                  col2 in varchar2,
                                  col3 in varchar2,
                                  viewname in varchar2 default 'v_tmp')
as
  sqlstr varchar2(2000):='create or replace view '||viewname||' as select '||col1||' ';
  c1 sys_refcursor;
  v1 varchar2(100);
begin
  open c1 for 'select distinct to_char('||col2||') from '||tabname;
  loop
    fetch c1 into v1;
    exit when c1%notfound;
    sqlstr:=sqlstr||'
  ,max(decode('||col2||','''||v1||''','||col3||'))"'||v1||'"';
  end loop;
  close c1;
  sqlstr:=sqlstr||' from '||tabname||' group by '||col1;
  execute immediate sqlstr;
end proc;

--测试表
create table tab as select 36 counter,'英语' subject,'一班' class from dual
  union all select 44,'英语','二班' from dual
  union all select 44,'数学','二班' from dual
  union all select 33,'语文','一班' from dual
  union all select 39,'语文','三班' from dual; 
select * from tab;

--触发器
create or replace trigger tg_tab
after insert or delete or update on tab
declare
PRAGMA AUTONOMOUS_TRANSACTION;
begin
proc('tab','subject','class','counter');
commit;
end;

--测试
delete from tab where counter=44;
select * from v_tmp;

SUBJECT	一班	三班	二班
英语	36		
语文	33	39	

rollback;
select * from v_tmp;

SUBJECT	一班	三班	二班
数学			44
英语	36		44
语文	33	39	

[小灰狼W]

大概知道是怎么回事了..
自治事务允许你离开调用的事务上下文，执行一个独立的事务，然后返回调用的事务而不会影响到调用事务的状态。自治事务和调用事务不同，只有提交的事务才会在事务见共享。
触发器被触发，执行过程的时候，由于事务尚未提交，自治事务里看不到修改后的结果，所以生成的视图还是修改前的
试试修改表之后，commit，再执行delete from TABLENAME where 1=2
使触发器再触发一次过程，然后看看视图是否正常
想想有没有更好的解决办法

[Melissa1986]

嗯，虽然问题我还没有弄掉，但是收益非浅！谢谢你呀！

[humanity]

我觉得是先想明白，人家触发器本身是跟外面一个事务，如果你需要另开一个事务当然就相当于约定了不会直接再修改外面的事务更新的表了。不然就很奇怪了，说不定有潜在问题呢。

[小灰狼W]

没有什么好办法
去掉触发器，在前端修改完数据提交的时候执行一次过程吧..

[Melissa1986]

嗯，我写到前端去了，解决掉了!本来想在数据库中解决的,谢谢大家！结贴了！

[qxf_good]

trigger声明成事务触发器后 在存储过程里面也要加 commit吧