Re: [sqlite] Trigger update of multiple columns

[John Stanton]

You would have a better database if you normalize it and not do what you 
propose.

T&B 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.

Thanks,
Tom


----------------------------------------------------------------------------- 

To unsubscribe, send email to [EMAIL PROTECTED]
----------------------------------------------------------------------------- 




-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------