Skip to Main Content

Trigger on multirow insert

yomanFeb 23 2012 — edited Feb 23 2012
Hi all,

My DB is Oracle 10g XE and building app using Apex 4.1 on WinXp

I have a table called ups_data with columns as below
cell_id number (contains cell number from 1 to 200)
read_date date
loc_id varchar2
v1 number
v2 number

In one single insert, a user inserts 200 rows of data (for each cell 1 to 200) while read_date and loc_ID remain same for all 200 entries and different values for v1 & v2 are inserted. I want these 200 entries to be processed by calculating avg(v1) and avg(v2) on them and storing results in some table. The query I want to run is
select avg(v1), avg(v2) from ups_data where read_date=(same as in the data entered)
and loc_id=(same as in the data entered)
Now the problem is that I cant address these 200 entries (1 set) by using statement trigger as I cant use :new.loc_id and :new.read_date in the statement trigger. And if I use row level trigger I get it triggered 200 times for each row.

Is there a way by which I can trigger it only once, but still fetch the same loc-id and read_date entered via these entries.

Thanks in advance
Post Details
Added on Feb 23 2012