Re: Update inside (Insert) Trigger and Unique constraint...

On Wednesday 21 January 2004 20:12, D. Dante Lorenso wrote:
> NEVERMIND...  This is not a trigger problem.  It's a unique
> constraint problem...  If I have a unique constraint on
> a column like 'afile_version', and want to do an update on
> that column to add one to each number, is there a way to
> add an 'ORDER BY' to the update?
[snip]
>     FOR my_rec IN
>         SELECT afile_id
>         FROM audio_file
>         ORDER BY afile_version DESC
>     LOOP
>         /* roll back the version... */
>         UPDATE audio_file SET
>             afile_version = afile_version + 1
>         WHERE afile_id = my_rec.afile_id;
>     END LOOP;

This was mentioned in the last couple of weeks on one of the lists - don't
know which. Someone suggested doing UPDATE ...version=-version followed by
UPDATE ...version=(-version)+1

> And that does the trick, but I guess I might also be able
> to do something like this?:

Nope - or rather, if it does work I think it's down to chance.

>     UPDATE audio_file SET
>         afile_version = afile_version + 1
>     WHERE afile_id IN (
>         SELECT afile_id
>         FROM audio_file
>         ORDER BY afile_version DESC
>     );

PS - this is really a bug, but it doesn't seem to bite very often, and there
are work-arounds, so it hasn't reached the top of any developer's list yet.

--
  Richard Huxton
  Archonet Ltd