Re: Update col to Y set all other to N in trigger.
Date: Fri, 31 Jan 2020 10:03:00 +0000
Message-ID: <LNXP265MB156285ADADA13FE1F6907961A5070_at_LNXP265MB1562.GBRP265.PROD.OUTLOOK.COM>
What's supposed to happen if two users are view the same data at the same time and decide to pick different rows as the default ?
Regards
Jonathan Lewis
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Ethan Post <post.ethan_at_gmail.com> Sent: 31 January 2020 00:43
To: Andy Sayer
Cc: oracle-l
Subject: Re: Update col to Y set all other to N in trigger.
In this scenario there is a form user is filling out. When they select the single row which is default, all other rows must now be not default. I know one trick (schedule one time job to run right away which sets the other rows to N) but thought there was a more elegant solution.
On Thu, Jan 30, 2020 at 6:14 PM Andy Sayer <andysayer_at_gmail.com<mailto:andysayer_at_gmail.com>> wrote: Instead of firing an update statement from within the trigger you would just set the value within the changed row if necessary.
Something like
If :new.pk<http://new.pk> = 1 then
:new.col='Y';
Else
:new.col='N';
End if;
Of course, the other option is to ensure your code to update the table always goes through some procedure which knows the rules, a so called Transactional API (XAPI). You could also add a check constraint to enforce the rule so that an error occurs when an attempt to violate it is made.
Hope that helps,
Andrew
On Thu, 30 Jan 2020 at 23:52, Ethan Post <post.ethan_at_gmail.com<mailto:post.ethan_at_gmail.com>> wrote: I should recall how to do this but been a while.
I need the best way to
update foo set col='Y' where pk=1;
And have a trigger automatically...
update foo set col='N' where pk !=1;
While avoid mutating table error.
This one seems hard to google the answer to.
Thanks,
Ethan
e-t-h-a-n.com<http://e-t-h-a-n.com>
_at_poststop
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Jan 31 2020 - 11:03:00 CET