Thread: Please advise on this trigger function
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.
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.
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.
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.klaver@aklaver.com