MySQL Forums :: Stored Procedures :: Using Stored Proceedure to add rows to lookup table
Using Stored Proceedure to add rows to lookup table
Posted by: John Russell
Date: May 03, 2012 10:26AM
I have a lookup table that I use to join 3 tables.
Each of the t1_id, t2_id, t3_id in the lookup_table are FKs of the other 3 tables. Only t1_id is required, the other two can be null.
There's another lookup that joins TABLE_2 and TABLE_3
What I'd like to able to do is create a stored procedure that triggers when a new row is added to LOOKUP_T1_T2_T3, which if the row contains data for t2_id, looks up which t3_id(s) are linked to that t2_id in LOOKUP_T2_T3 and inserts it or them into new rows in LOOKUP_T1_T2_T3.
I'm having some problems trying to do this.
Firstly, running the stored procedure causes a recursive error.
Secondly, calling the stored procedure with a trigger causes an error saying "can't update table in stored function because it's already used by a statment which invoked the stored function."
Is there a way of doing what I want to do?
Any help gladly appreciated.
Edited 1 time(s). Last edit at 05/03/2012 11:01AM by John Russell.
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.