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;
uj5u.com熱心網友回復：
-- 這個要寫兩個觸發器
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 dropped
SQL>
uj5u.com熱心網友回復：
能簡單說下思路么 我見了2個觸發器還是不能解決 求指教我這么建的
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;
uj5u.com熱心網友回復：
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;
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;
不是很理解題主的意思。你是要在更新前后都執行兩次，那你就寫兩個觸發器不就好了嗎？
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/97849.html
標籤：開發