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

As far as I understand it, you are writing the application.

So instead of your application supplying that single-row update, have it supply whatever is needed to get the data in the proper shape when the user hits submit, in a single transaction.

On Fri, Jan 31, 2020 at 8:55 AM Sayan Malakshinov <xt.and.r_at_gmail.com> wrote:

> 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
>>>>>>>>
>>>>>>>

-- 
//
zztat - The Next-Gen Oracle Performance Monitoring and Reaction Framework!
Visit us at zztat.net | _at_zztat_oracle | fb.me/zztat | zztat.net/blog/

--
http://www.freelists.org/webpage/oracle-l