Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

gather records in compound trigger and process them

TelluriumDBAJul 10 2019 — edited Jul 10 2019

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.

Comments

Post Details

Added on Jul 10 2019
7 comments
493 views