-
I am relatively inexperienced in PL/SQL and hence I need help
I have two tables like this:
GC_COUNT
FID CID ANNOTAT
____________________________
1 1 1, 1-5
1 2 2, 2-10
1 3 3, 10-15
2 1 35, 1-4
2 2 45, 3-5
All varchar and the combination of FID and CID is unique.
For each FID the application might generate any number of CID's.
What I need to do is write a trigger after an FID is inserted into this table which will capture the ANNOTAT of all CID's for a particular FID and concatenate them and update another table like this:
GC-CBL
FID ANNOTAT
__________________________________
1 1, 1-5 2, 2-10 3,10-15
2 35, 1-4 45, 3-5
Here FID is the PK.
Thanks for all hekp rendered.
Nizar
-
The trigger you want would probably cause the "mutating table" error as it would read the same table on which it is firing.
Autonomous transactions may help to avoid that error but the trigger, as it runs it its own transaction, would not see just inserted (or any inserted and uncommited) rows.
Perhaps the trigger that adds the just inserted gc_count.annotat to the end of gc_cbl.annotat would meet your needs. But then you also need handle DELETE and UPDATE actions on the gc_count table which could be painful.
Ales
