Thread: Triggers per transaction, workaround? prospects?

Hi,

I don't suppose that the todo item:

Referential Integrity
   o Add deferred trigger queue file (Jan)

Means that there will be a statement like:

CREATE TRIGGER ... FOR EACH TRANSACTION

?

I frequently encounter situations where the
database is only 'good' when all the the statements
in the transaction have completed.  (Duh, isn't this
the _point_ of transactions?)  The latest is I want
a per foreign key sequence number column, 1, 2, 3, etc., say,
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
> = the first mis-placed sequence number.  Given the
existance of other rows which reference the sequenced rows,
this is not a pretty picture.  It'd be nice to be able to
put a series of UPDATE statements in a transaction
and have a trigger check the state of the database when the
transaction commits.  (And be able to raise an exception
if the rules are violated.)

Has anybody else encountered problems like this and come up
with any solutions?

Stupid question:
If triggers automatically created to support REFERENCES
constraints can be deferred to execute on transaction commit,
and see the results of the statements executed prior to
the COMMIT, then why can't this be done for regular
triggers?

Regards,

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

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.

Greg

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@meme.com>
Free Software:  "You don't pay back, you pay forward."
                  -- Robert A. Heinlein