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

Strictly speaking there is a way to see uncommitted changes in autonomous transactions - you can open cursor in the main transaction and use it in autonomous transaction. But anyway I don't like triggers and wouldn't suggest to use them, especially in this case. I agree with Andy's recommendation to use procedures instead.

пт, 31 янв. 2020 г., 4:44 Andy Sayer <andysayer_at_gmail.com>:

> 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