Oracle triggers in a trigger call a stored procedure, execute the stored procedure before the data update, think such as data updated in execute the stored procedure, what method?
I stored procedure is put table data updated in the other list
The create or replace procedure pro_daily_report_gs is
The begin
The 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;

CodePudding user response:

 
- this will write two trigger 

SQL> 
SQL> Create table test (int id, name varchar (10)); 
The Table created 
SQL> The create table test_bak as select * from the test; 
The Table created 
SQL> Insert into test values (100, 'China'); 
1 row inserted 
SQL> Create the trigger tri_test_before 
2 before the insert or update or delete on the test 
3 the begin 
4 the insert into test_bak select * from the test; 
5 the end; 
6/
The Trigger created 
SQL> Create the trigger tri_test_after 
2 after the insert or update or delete on the test 
3 the begin 
4 the insert into test_bak select * from the test; 
5 the end; 
6/
The Trigger created 
SQL> Insert into test values (200, 'USA'); 
1 row inserted 
SQL> Select * from the 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> 

CodePudding user response:

Can simply the idea that I saw two trigger still can't solve please teach
I built this
The CREATE OR REPLACE the TRIGGER SEC_DAILY_UPD_BEFORE_TRI
Before the update on za_report_daily
Declare
Pragma autonomous_transaction;
The begin
If updating then
The delete from za_report_daily_gs;
Insert into za_report_daily_gs (dwmc2, rq, kjdy_fas kjdy_yyl)
The 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;

CodePudding user response:

 CREATE OR REPLACE the TRIGGER SEC_DAILY_UPD_BEFORE_TRI 
Before the update on za_report_daily 
Declare 
Pragma autonomous_transaction; 
The begin 
If updating then 
The delete from za_report_daily_gs; 
Insert into za_report_daily_gs (dwmc2, rq, kjdy_fas kjdy_yyl) 
The 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 the TRIGGER SEC_DAILY_UPD_BEFORE_TRI 
After the update on za_report_daily 
Declare 
Pragma autonomous_transaction; 
The begin 
If updating then 
- should not need to delete the table is the delete from za_report_daily_gs; 
Insert into za_report_daily_gs (dwmc2, rq, kjdy_fas kjdy_yyl) 
The 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; 

Not very comprehension main point, you're going to be updated twice before and after, then you can write two trigger is not good?