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

This is working, pragma autonomous_transaction with after update trigger.

create or replace trigger saas_role_update

   after update on saas_role
   for each row
declare

   pragma autonomous_transaction;
begin

   if :new.is_default = 'Y' then

      update saas_role set is_default='N' where role_id not in (:new.role_id) and is_default='Y';

   end if;
   commit;
exception

   when others then

      rollback;
      raise;

> I wouldn’t trust that solution to work well enough when this gets used by
> more than one user in a few minutes.
>
> You should code your form so that it submits the update for all necessary
> rows. You should also create a unique function based index to enforce this
> uniqueness rule. Only 1 row can be the default so you could have the index
> on decode(is_default,'Y',1), or if it depends on a group then you would put
> the group reference there instead of the hard coded 1.
>
> Alternatively, the default row should be referenced somewhere else. If
> this is per group then you would have a group header table which contains
> the pk to the default row in your other table.
>
> Thanks,
> Andrew
>
> On Fri, 31 Jan 2020 at 00:43, Ethan Post <post.ethan_at_gmail.com> wrote:
>
>> 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