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@xxxxxxxxx> 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@xxxxxxxxx> 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@xxxxxxxxx> 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@xxxxxxxxx> 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
@poststop
