Hi all,
My table has two columns which I am concerned for this discussion: user_email and deactivate_date. A user is not allowed to have multiple active accounts and that business rule is enforced via a trigger
which has following SQL query .
SELECT user_email , COUNT(*)
FROM test_table
WHERE (deactivate_dateIS NULL OR deactivate_date>SYSDATE)
GROUP BY user_email
HAVING COUNT(*) > 1;
The problem we recently encountered is that when a user record is created by Java application which mistakenly allowed double clicks , two user records get created simultaneously (with exact same timestamp) with deactivate_date as NULL.
How did this bypass the trigger and what should I do better to avoid this from the backend?
Thanks,