Skip to Main Content

APEX

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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Validation ignored on Insert but fires on Update

John like many othersAug 12 2020 — edited Aug 12 2020

Hello

I have created a validation for an Interactive Grid region ensuring an entered key will not collide with it's period (validfrom/validto) of another existing record with same key. Example:

Imagine this record does already exist in the DB:

Key           Valid from             Valid to

12345        01/01/2020            31/12/2020

Now I enter new entry in the Interactive Grid with these values:

Key           Valid from             Valid to

12345       05/05/2020            31/12/2020

On insert that new record this Validation will be triggered (No Rows returned) to check if the key to be inserted already exists and overlaps any other key with same key_value

SELECT 1

FROM <target_table>

WHERE rule_id != :RULE_ID  -- PK (to exclude existence check against itself)

AND key = :KEY

AND TO_DATE(:VALIDFROM, 'DD/MM/YYYY') BETWEEN validfrom AND validto

On Insert the validation accepts (ignores) there is an existing key with same value (12345) and overlapping period (valid from / valid to) but it should stop and output the entered error message.

So the record will be inserted and no error message popping up. Now I change the valid from of the entered entered and save it again which leads to an update. This time the validation rule fires correctly!

I have played around with the Validation settings:

- Always execute: yes

- Execution Scope: For created and modified rows / for all submitted rows

All over: the validation is working as stated on updates.

Is there anything else I have to consider to make the validation working on insert?

This post has been answered by John like many others on Aug 12 2020
Jump to Answer

Comments

Post Details

Added on Aug 12 2020
2 comments
247 views