oracle触发器 在触发器中调用存储过程,在数据更新之前就执行存储过程,想等数据更新后在执行存储过程,有什么办法么
我的存储过程是把表里的数据更新后放入别的表里
create or replace procedure pro_daily_report_gs is
begin
delete from za_report_daily_gs;
insert into za_report_daily_gs(dwmc2,rq,kjdy_fas,kjdy_yyl)
(select dwmc2,rq, sum(kjdy_fas), sum(kjdy_yyl) from za_report_daily group by dwmc2,rq ) ;
commit;
end pro_daily_report_gs;
我的存储过程是把表里的数据更新后放入别的表里
create or replace procedure pro_daily_report_gs is
begin
delete from za_report_daily_gs;
insert into za_report_daily_gs(dwmc2,rq,kjdy_fas,kjdy_yyl)
(select dwmc2,rq, sum(kjdy_fas), sum(kjdy_yyl) from za_report_daily group by dwmc2,rq ) ;
commit;
end pro_daily_report_gs;
-- 这个要写两个触发器SQL>
SQL> create table test(id int, name varchar(10));
Table created
SQL> create table test_bak as select * from test;
Table created
SQL> insert into test values (100, 'china');
1 row inserted
SQL> create trigger tri_test_before
2 before insert or update or delete on test
3 begin
4 insert into test_bak select * from test;
5 end;
6 /
Trigger created
SQL> create trigger tri_test_after
2 after insert or update or delete on test
3 begin
4 insert into test_bak select * from test;
5 end;
6 /
Trigger created
SQL> insert into test values (200,'usa');
1 row inserted
SQL> select * from test;
ID NAME
--------------------------------------- ----------
100 china
200 usa
SQL> select * from test_bak;
ID NAME
--------------------------------------- ----------
100 china
100 china
200 usa
SQL> drop table test purge;
Table dropped
SQL> drop table test_bak purge;
Table droppedSQL>
我这么建的
CREATE OR REPLACE TRIGGER SEC_DAILY_UPD_BEFORE_TRI
before update on za_report_daily
declare
pragma autonomous_transaction;
begin
if updating then
delete from za_report_daily_gs;
insert into za_report_daily_gs(dwmc2,rq,kjdy_fas,kjdy_yyl)
select dwmc2,rq, sum(kjdy_fas), sum(kjdy_yyl) from za_report_daily group by dwmc2,rq ;
end if;
commit;
end SEC_DAILY_UPD_BEFORE_TRI;
before update on za_report_daily
declare
pragma autonomous_transaction;
begin
if updating then
delete from za_report_daily_gs;
insert into za_report_daily_gs(dwmc2,rq,kjdy_fas,kjdy_yyl)
select dwmc2,rq, sum(kjdy_fas), sum(kjdy_yyl) from za_report_daily group by dwmc2,rq ;
end if;
commit;
end SEC_DAILY_UPD_BEFORE_TRI;
CREATE OR REPLACE TRIGGER SEC_DAILY_UPD_BEFORE_TRI
after update on za_report_daily
declare
pragma autonomous_transaction;
begin
if updating then
---应该不需要删除表了 delete from za_report_daily_gs;
insert into za_report_daily_gs(dwmc2,rq,kjdy_fas,kjdy_yyl)
select dwmc2,rq, sum(kjdy_fas), sum(kjdy_yyl) from za_report_daily group by dwmc2,rq ;
end if;
commit;
end SEC_DAILY_UPD_BEFORE_TRI;
不是很理解题主的意思。你是要在更新前后都执行两次,那你就写两个触发器不就好了吗?