On Tue, Mar 18, 2008 at 12:17 AM, MB Software Solutions General Account < [EMAIL PROTECTED]> wrote:
> MB Software Solutions General Account wrote: > > 3 tables involved: Products (details about the tire in this case), > > Locations (store locations), and Inventory (contains FKs to both > > Products and Locations and has current count in stock of Product at > > Location). > > > > I want to create an Insert trigger on Products that automatically > > creates an Inventory record for EACH record in the Location table. For > > example, if I create a Product with PK=44, and there are 3 records in > > the Locations table (PKs = 1, 2, and 3), then I want 3 records inserted > > into the Inventory table, with the keys 44/1, 44/2, and 44/3. > > > > Does anyone know a way to get a "recordset" from the Locations table > > inside the stored proc and then cycle through it creating these records > > for the new Product ID? I knew how to do this in PL/SQL (Oracle 9), but > > MySQL is new territory for me. > > > > tia! > > --Michael > > > > > > Looks like I need a cursor: > http://dev.mysql.com/doc/refman/5.0/en/cursors.html > > This is very cool stuff...it's true what other devs say---it IS neat to > learn new things! (I knew that already, though! lol....) > --------------------------------------------------------------------------------------- Cursors are very heavy in SQL processing and should only be used when there is NO OTHER possible way. I would use GUIDs instead of ints as keys. Sure they are big and ugly but they work really well! Secondly I would never mix up the keys in the 44/3 manner. Why should you need the reference for the product in the location? You already have that in the Inventory. You need a pair of insert statements with your Product insert. <http://dev.mysql.com/doc/refman/5.0/en/using-triggers.html> Looks like a straight shot to do them in mySQL. syntax is very similar in SQL Server. I would use the After insert instead of Before insert in case your schema has a dependency on an existing "product" HTH -- Stephen Russell Sr. Production Systems Programmer Mimeo.com Memphis TN 901.246-0159 --- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html --- _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
