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

Autonomous transactions triggers are another easy way to logically corrupt your data. Importantly they can’t see the changes you’ve just made (they’re operating from a separate transaction so can’t see your uncommitted changes).

They are probably 99% of the reason why triggers are seen as bad in general.

On Fri, 31 Jan 2020 at 01:33, Ethan Post <post.ethan_at_gmail.com> wrote:

> 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