On 6/17/07, T&B <[EMAIL PROTECTED]> wrote:
Hi All,I have a pretty standard sales tracking database consisting of tables: Products - Each row is a product available for sale. Includes fields: Code, Buy, Sell, Description Sales - Each row is a sale made to a customer. Includes fields: Ref, Customer Sale_Products - Each row is an product (many) included in a sale (one). Includes fields: Sale_Ref, Code, Buy, Sell, Description Now, when I add a new Sale_Products row and assign a product Code to it, I want to trigger it to auto enter the Buy and Sell prices, and the description, by looking up the related Product (ie where Sale_Products.Code = Products.Code) How can I do this? I have something like this: create trigger Update_Sale_Products_Code after update of Code on Sale_Products begin update Sale_Products set Buy = (select Buy from Products where Products.Code = new.Code) , Sell = (select Sell from Products where Products.Code = new.Code) , Description = (select Description from Products where Products.Code = new.Code) where rowid=new.rowid ; end It works, but it's unnecessarily slow, since it takes a while to look up the huge Products table (which is actually a UNION ALL of various supplier catalogs), and it's looking it up for each updating field (and I have more fields to lookup than shown in this example). It would be more efficient to look it up once to find the corresponding product (according to Products.Code = new.Code), but I'm stumped as to how to do that. I tried: create trigger Update_Sale_Products_Code after update of Code on Sale_Products begin update Sale_Products set Buy = (select Buy from Products) , Sell = (select Sell from Products) , Description = (select Description from Products) where rowid=new.rowid and Products.Code = new.Code ; end But that fails, and seems a bit ambiguous anyway. It seems to need some kind of JOIN, but I can't see provision for it in the UPDATE syntax. There must be a much simpler way that I'm overlooking. Please enlighten me.
Why are you repeating the Code, Buy, Sell, and Description columns in the Sale_Products table when they already exists in the Products table? I can't decipher what the Buy and the Sell columns are supposed to hold in your scenario, but here is what I would do (you can always add other columns, but these would be the minimum necessary, and these assume that the "buy" column is the price I paid for the item, and "sell" column is the price I get for it) -- CREATE TABLE products ( product_id INTEGER PRIMARY KEY, buy REAL, sell REAL, desc TEXT ); CREATE TABLE customers ( customer_id INTEGER PRIMARY KEY, .. other customer info columns .. ); CREATE TABLE sales ( sale_id INTEGER PRIMARY KEY, product_id INTEGER, -- FK to products table customer_id INTEGER -- FK to customes table ); Then, as a sale is made to a customer, I would INSERT INTO sales (product_id, customer_id) VALUES (?, ?); and fill up the bindvals with the customer_id I picked up from the customer's log in or however you identified the customer, and the product_id from my application. There are no duplicates above, everything is normalized properly. Also notice the naming of the columns... there is a consistency... ????_id is always the first row in a table, and is always INTEGER PRIMARY KEY. To another reader, it is very clear what is happening without requiring lots of explanations. And, no TRIGGERs are involved. -- Puneet Kishor http://punkish.eidesis.org/ Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/ Open Source Geospatial Foundation http://www.osgeo.org/education/ S&T Policy Fellow, National Academy of Sciences http://www.nas.edu/ --------------------------------------------------------------------- collaborate, communicate, compete ===================================================================== ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
