Re: Please advise on this trigger function

On 03/30/2016 01:09 PM, John Stewart wrote:
> Good day,
>
> I have two tables, table1 & table2 with two trigger functions set on
> table1, on before & after insert. table1 contains a foreign key
> reference to table2, called building_id.

What are the actual schema definitions for the tables?

>
> when a row is inserted into table1, building is in text format, I need
> the after-trigger to lookup this text value in table2, if it exists,
> retrieve the id & update table1 with it, else if it doesn't exist insert
> it & update table1 with the new id.

Seems you are doing this backwards, need to see the above schema
definitions to be sure.

>
>
> I could do this in the before-trigger, but how do you rollback the newly
> created row in table2 (if it's a new value) if insert failed on table1.
>
> my problem is passing values between the before & after triggers, I
> could retrieve all the required columns' values in after-trigger using
> NEW.* but what about building_id? I could set it temporarily to NULL in
> before-trigger because clearly the foreign column cannot accept a
> textual-value, then do the lookup with table2 in after-trigger, but how
> to sustain the original building textual value?
>
>
> Thank you.

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com