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

Thanks for all the feedback, just getting caught up here. There will only be one user accessing this data in 99.9% of cases, still I know, not an ideal solution. I have thought about sep. table for the value and that would work well but breaks the UI. Trying to make this as simple as an APEX interactive grid for the UI. Set Y or N and everything else fixes. What I can do is create a validator on the UI which says only one Y and make the user change any other value to N manually. This will work and for a small set of rows, like in this case, is a fine solution. I can pull (max) to ensure if there are more than one value I only get one, so code works, and I can added error log if there are ever >1 found in same function I grab max. I think I will go with that solution in this case but it is still one in my mind that gets rather complex for something that "seems" so simple. Again thanks for all the feedback. You helped me think through this issue.

On Fri, Jan 31, 2020 at 2:48 PM Tefft, Michael J <Michael.J.Tefft_at_snapon.com> wrote:

> Ø 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.
>
>
>
> This is absolutely correct.
>
> Use another table (with a single row, if that’s what it is) to contain the
> ‘latest’ or ‘default’ value.
>
> Putting an indicator in the table, which can be ‘Y’ on one and only one
> row, will not lead to joy.
>
>
>
> Mike
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Andy Sayer
> *Sent:* Thursday, January 30, 2020 7:57 PM
> *To:* Ethan Post <post.ethan_at_gmail.com>
> *Cc:* oracle-l <oracle-l_at_freelists.org>
> *Subject:* Re: Update col to Y set all other to N in trigger.
>
>
>
> 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