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