Thread: Using a trigger with an object-relational manager

I'm using Ruby on Rails and have two tables, A and B.  Every row in A
needs a corresponding row in B.  A also contains a FK pointing to B.

I created a before insert trigger on A that inserts a new row in B, and
sets the FK in A.  This seems to be running fine.

The problem is that the new A object that rails gets does not see the
effect of the trigger.  If I call the "reload" method, then everything
is ok.

I'm not sure if this is a rails specific problem, or if there is an
adjustment I can make to my SQL to avoid the need for the reload.

Can I somehow put the creation of A, B and setting the FK in a
transaction so that rails receives the row from A after the trigger
completes?  If so, I'm not sure the best way to do that.

Thanks for any suggestions!

On May 22, 2007, at 21:21 , Rick Schumeyer wrote:

> The problem is that the new A object that rails gets does not see
> the effect of the trigger.  If I call the "reload" method, then
> everything is ok.

As ActiveRecord (which I'm assuming you're using with Rails) has no
idea of what is going on in the database (ActiveRecord doesn't have a
concept of triggers), there's no way for it to see the effect of the
trigger without querying the database for the latest information
(which is what you're doing with reload).

Michael Glaesemann
grzm seespotcode net

> I'm using Ruby on Rails and have two tables, A and B.  Every row in A
> needs a corresponding row in B.  A also contains a FK pointing to B.
>
> I created a before insert trigger on A that inserts a new row in B, and
> sets the FK in A.  This seems to be running fine.

    So, A has a b_id field linking to B ?

    If you need a 1-1 relationship, you could try instead to use the same
primary key in B than in A :

A : id SERIAL PRIMARY KEY
B : id INTEGER PRIMARY KEY (not SERIAL)

Then, AFTER INSERT trigger on A checks the value the sequence put in A.id
and inserts in B with this value as the PK.

    Postgres has INSERT ... RETURNING which is a very clean and elegant
solution but Rails never heard about it...

On Wed, 23 May 2007 14:41:00 +0200, Rick Schumeyer <rschumeyer@ieee.org>
wrote:

> Actually, the situation is slightly more complicated.  It's more like I
> have tables A1, A2, and A3 each of which must have a corresponding row
> in B.  So each of A1, A2 and A3 has a BEFORE INSERT trigger that creates
> a row in B and sets a FK in A1 (or A2 or A3).  So I can't just use the
> same PK in both the A tables and B.

    It's a hack, but A1, A2, A3 etc could all use the same sequence to
generate their PK...

    The best would be to patch rails so it uses INSERT RETURNING.

I just realized that I was having a slight brain-freeze earlier.
ActiveRecord creates an A object first, then tells pg to INSERT it, but
nothing is actually returned by the INSERT statement.  (I assume that
Hibernate et. al would be the same).  So of course it does not see the
result of the trigger, since it does not receive a result from the
database...until reload is called.

At least I assume that is correct.
Michael Glaesemann wrote:
>
> On May 22, 2007, at 21:21 , Rick Schumeyer wrote:
>
>> The problem is that the new A object that rails gets does not see the
>> effect of the trigger.  If I call the "reload" method, then
>> everything is ok.
>
> As ActiveRecord (which I'm assuming you're using with Rails) has no
> idea of what is going on in the database (ActiveRecord doesn't have a
> concept of triggers), there's no way for it to see the effect of the
> trigger without querying the database for the latest information
> (which is what you're doing with reload).
>
> Michael Glaesemann
> grzm seespotcode net

Actually, the situation is slightly more complicated.  It's more like I
have tables A1, A2, and A3 each of which must have a corresponding row
in B.  So each of A1, A2 and A3 has a BEFORE INSERT trigger that creates
a row in B and sets a FK in A1 (or A2 or A3).  So I can't just use the
same PK in both the A tables and B.

PFC wrote:
>
>> I'm using Ruby on Rails and have two tables, A and B.  Every row in A
>> needs a corresponding row in B.  A also contains a FK pointing to B.
>>
>> I created a before insert trigger on A that inserts a new row in B,
>> and sets the FK in A.  This seems to be running fine.
>
>     So, A has a b_id field linking to B ?
>
>     If you need a 1-1 relationship, you could try instead to use the
> same primary key in B than in A :
>
> A : id SERIAL PRIMARY KEY
> B : id INTEGER PRIMARY KEY (not SERIAL)
>
> Then, AFTER INSERT trigger on A checks the value the sequence put in
> A.id and inserts in B with this value as the PK.
>
>     Postgres has INSERT ... RETURNING which is a very clean and
> elegant solution but Rails never heard about it...