MySQL Forums
Forum List  »  Stored Procedures

Using Stored Proceedure to add rows to lookup table
Posted by: John Russell
Date: May 03, 2012 10:26AM

Hiya,

I have a lookup table that I use to join 3 tables.

TABLE_1
-----------
t1_id
t1_name

TABLE_2
-----------
t2_id
t2_name

TABLE_3
-----------
t3_id
t3_name

LOOKUP_T1_T2_T3
-------------
lookup_id
t1_id
t2_id
t3_id

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

LOOKUP_T2_T3
------------
lookup_id
t2_id
t3_id


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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Using Stored Proceedure to add rows to lookup table
1643
May 03, 2012 10:26AM


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.