Validation ignored on Insert but fires on Update

John like many others

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?

John like many others

While describing the problem I had an idea what could cause the problem and I could solve it.

The mentioned validation SQL has no :RULE_ID on Insert that leads to check against NULL (which is always bad and leads to unpredictable results). This correction made it working on Insert:

WHERE rule_id != NVL(:key, -1)

I post this so it might help someone else in the future.

John like many others

I missed to mention that I even can't delete the inserted key if it overlaps the time range of an existing one although Execution Scope is set to 'For created and modified rows'

John like many others

While describing the problem I had an idea what could cause the problem and I could solve it.

The mentioned validation SQL has no :RULE_ID on Insert that leads to check against NULL (which is always bad and leads to unpredictable results). This correction made it working on Insert:

WHERE rule_id != NVL(:key, -1)

I post this so it might help someone else in the future.