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

Hah, maybe not. Will update when I get something working :)

On Thu, Jan 30, 2020 at 7:30 PM Ethan Post <post.ethan_at_gmail.com> wrote:

> 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;
> end;
> /
>
> On Thu, Jan 30, 2020 at 6:57 PM Andy Sayer <andysayer_at_gmail.com> wrote:
>
>> 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