Re: Update col to Y set all other to N in trigger.

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