i have the following table. when a record is being inserted, i need to make sure the latest record of a given batch is updated as isLatest = 1. the update also should update the previous isLatest to 0 as well.
tableA
(
batchNumber number(10),
runNumber number(10),
serialNumber number(10),
isLatest number(1)
)
so i write a compound trigger
i need the trigger to be as follows:
after each row is
begin
<gather records which were inserted to objectA>
end
after statement is
select a.batchNumber, a.runNumber, a.serialNumber from tableA a inner join objectA o on a.batchNumber = o.batchNumber and a.isLatest = 0;
the above will be collected to an object (objectB).
now, i need to filterout the latest records. so i do this :
select batchNumber, runNumber, serialumber, row_number() over( partition by batchNumber order by runNumver desc, serialNumber desc) as LatestRank from objectB
the above will be collected to objectC
now, object C has to be updated with the TableA
merge into TableA ta using (select * from objectC where LatestRank <>1) R
on (ta.batchNumber = r.batchNumber and ta.runNumber = r.runNumber and ta.serialNumber = r.serialNumber)
when matched then
update set a.isLatest = 0;
===================================
i am confused as what method to use for the "objects". i tried both ref cursor and table type but no luck. Appreciate any help in this regard.