Validation of row combinations

[BJCooperIT]

I have a need to validate row combinations of a child block.
[ol][li]There must be one child row[/li]
[li]There can be no duplicate IDs in the MSID table except for one value, 9999, that may be used by any DIST.[/li][/ol]

(Parent Block = DIST, Child Block = MSID) These are valid key combinations in MSID:

DIST   ID
1      4589
1      9999
2      1234
2      9999    <-- OK

These are not:

DIST   ID
3      1234
4      [COLOR=red]1234[/color]   <-- duplicate

The problem is complicated by the navigation restriction in WHEN-VALIDATE triggers. The original developer put the validation code on the KEY-COMMIT trigger. The code did a first_record and then "walked" thru the rows using next_record with complicated logic to prevent duplicates.

I pointed out 2 flaws:
[ol][li]1. If all the rows were not displayed in the block (due to clear_record, query-by-example, etc.) that the validation would not be correct.[/li]
[li]There are several ways the data can be committed, such as during clear_block or enter_query thus the validation would not occur.[/li][/ol]

We tried putting the validation in database triggers to raise an application error, but received the error:

ORA-04091 table string.string is mutating, trigger/function may not see it

Cause: A trigger (or a user defined PL/SQL function that is referenced in this statement) attempted to look at (or modify) a table that was in the middle of being modified by the statement which fired it.

Action: Rewrite the trigger (or function) so it does not read that table.

The only other thing that occurs to me is to trap every way the data can be committed, and have KEY-COMMIT perform the validation procedure that would issue a SAVEPOINT, POST the changes, do the validation selects from the database, and then either COMMIT or ROLLBACK as appropriate. I don't like this solution, I have always told my developers that validation should never happen on a KEY-anything trigger.

Is there a better solution?

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Systems Project Analyst/Custom Forms & PL/SQL - Oracle/Windows

 

[BJCooperIT]

Forgot to mention that the form must navigate to the offending row.

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Systems Project Analyst/Custom Forms & PL/SQL - Oracle/Windows

 

[HimanB]

Hi,

Step1:What you may do over here is to first store all the values of PK's of detail block in an array(Only for changed or New records) & then validate them against each other.

Step2:Also store keys of deleted records in a separate array.

Step 3:Once this is done & no duplicates are found in Step 1 then validate the records in the array against Database.
If a match is found then check if that Key exists in Array created in Step 2.If yes then allow Processing further and delete that value from Step 2 array.
Else raise an error.

HTH
Regards
Himanshu

 

[sem]

Barbara,
IMO using database trigger is the only clean way because Oracle provides only statement-level read-consistency. Any other consecutive verifications do not guaranty that even the first condition is still valid. Thus some changes may occur between checking the condition and applying the changes. As for mutating error you should know how to handle it. If not - look for this specific word here or on the net. The idea is to save changes in some temporary location (collection package variable, temporary table) in pre- record-level trigger and then apply them after verification in post- statement-level trigger. Don't forget to set/reset some flag to avoid recursions and to clean that temporary location. Actually you may find a lot of working examples, unfortunately I have no that would be descriptive enough. Try to ask Santa Mufaza in "pure Oracle" fora, he should has a plenty of.
Just a small note: try to consider redesigning, as attributing unknown values to specific record is not the only solution. If I'm wrong regarding your original task please ignore this.

Regards, Dima

http://seminihin.narod.ru

 

[BJCooperIT]

HimanB,
Thanks for your approach.

Sem,
I, too, believe that the database trigger was the most appropriate way to achieve this. Actually, I have never personally encountered the mutating table error, but I know others that have. As far as design is concerned, this is a modification to an existing form, if I had the luxury of redesign, I certainly would do it. By the way, I never ignore your posts!

Both,
After a bit of research my developer and I decided to take the temp table approach. He took a different path with it than I would have, but it is working now. I appreciate the help both of you offered.

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Systems Project Analyst/Custom Forms & PL/SQL - Oracle/Windows