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> 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 = 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> 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
>> _at_poststop
>>
>

--
http://www.freelists.org/webpage/oracle-l