Re: Triggers per transaction, workaround? prospects?

On 2004.02.29 08:11 Gregory Wood wrote:
> Karl O. Pinc wrote:
>> a per-person counter, which must not contain any 'gaps'. I can do
>> this
>> so long as nobody every makes any mistakes
>> in sequencing, but once the sequence numbers are in place
>> there's no way to re-order the rows in a sequence
>> (the rows belonging to one person) without deleting
>> and re-creating all the rows with sequence numbers
>
> I can't answer your particular question, but you can always put an ON
> UPDATE CASCADE into your foreign keys... then if you *shudder* had to
> change those values, it should cascade out through all the attached
> rows in other tables.

Thanks for the reply, but that doesn't really help. It's not the
key I want to change, but the sequence number sequenced on a foreign
key. I want to write a trigger to ensure the sequence numbers
are per-foreign-key sequential, Paulovie Michal wants to have the system
generate the sequential (e.g. per-person) numbers.

Assume fkey is a foreign key (say person id) and seq is the sequence
column always starting with 1. Then the trigger code

PERFORM SELECT larger.fkey, larger.id
FROM table AS smaller, table AS larger
WHERE larger.id > 1
AND larger.fkey = smaller.fkey
AND larger.seq = smaller.seq + 1
IF FOUND THEN
...

_would_ check for sequentiality, if only it could be run when the
transaction completes. Otherwise, you can't update the seqeunce
numbers to fix mistakes. :-(

Of course, you'd also want the above code to run against those
rows that have changed, not the whole table. But I don't care
(much) as my database isn't that large. (A virtual NEW table would be
nice, with new rows. And maybe a corresponding OLD table. Ah-lah
sybase triggers.)

Would Paulovie Michal be able to store the next available sequence
on the (for example) person row and then use serializeable transactions
and a little BEFORE insert trigger function of his own that updates the
person
row in question and uses the value to alter the sequence number
on the inserted row? Or is postfix's serialization not up to this?

What I realy want to know is if per-transaction triggers are
anywhere in my future. (O'Reilly's SQL In A Nutshell says
that that's how PostgreSQL triggers works, but they're wrong.
:-( )

Thanks,

Karl <kop(at)meme(dot)com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein