triggers with multiple tables

CdnGal
CdnGal used Ask the Experts™
on
Hi, there...

(beginner user here)...need help (learning sql server 2000)

I have three tables, Customers (C), Orders (O), and Order_Line (OL).  OL.Order_number relates to O.Order_number(pk).  O.Customer_number relates to C.Customer_number(pk).  The customer table has a "balance" field.  I need to create a trigger to update the customer's balance when an order is placed.  The update is only to run if a single row is being added to the ORDER-LINE table.  

I sort of understand triggers, but so far have only done them when two tables directly linked are involved.  In this case, the number ordered and price are in the OL table.  These two multiplied together (to get the total) are to be added to the customers balance.  So in my code, I've included the following statement:

SET C.balance=C.balance+(OL.number_ordered*OL.quoted_price)

Where I'm stumped is my WHERE statement.  I'm totally clueless as to what to do?!  In my other examples, there is only one WHERE clause, like this example(i is inserted)
WHERE p.Part_number=i.Part_number

But in this case the table that has the field I'm updating isn't directly connected to the table that will trigger.  I'm confused.  Can someone help me.

Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
did you try

UPDATE C SET C.balance = C.balance +
Ol.Order_Grand_Total
FROM
Customer as C
inner join
inserted as i
on i.Customer_number = C.Customer_number
inner join
( select SUM(OL.number_ordered*OL.quoted_price) AS Order_Grand_Total, Order_number FROM Order_Line GROUP BY Order_number ) as Ol ON
i.Order_number = Ol.Order_number
GROUP BY

IF @@ERROR <> 0
   IF @@TRANCOUNT >= 1
       ROLLBACK TRANSACTION
ELSE
   IF @@TRANCOUNT >= 1
       COMMIT TRANSACTION


Cheers
removed extra group by, must have pasted it and did not expect to check it down below, corrected script.

UPDATE C SET C.balance = C.balance +
Ol.Order_Grand_Total
FROM
Customer as C
inner join
inserted as i
on i.Customer_number = C.Customer_number
inner join
( select SUM(OL.number_ordered*OL.quoted_price) AS Order_Grand_Total, Order_number FROM Order_Line GROUP BY Order_number ) as Ol ON
i.Order_number = Ol.Order_number

IF @@ERROR <> 0
  IF @@TRANCOUNT >= 1
      ROLLBACK TRANSACTION
ELSE
  IF @@TRANCOUNT >= 1
      COMMIT TRANSACTION


Cheers
one more fix,

UPDATE C SET C.balance = C.balance +
Ol.Order_Grand_Total
FROM
Customer as C
inner join
inserted as i
on i.Customer_number = C.Customer_number
inner join
( select SUM(OL.number_ordered*OL.quoted_price) AS Order_Grand_Total, Order_number FROM Order_Line GROUP BY Order_number ) as Ol ON
i.Order_number = Ol.Order_number

IF @@ERROR <> 0
 IF @@TRANCOUNT >= 1  -- this if needs closing "braces"
 BEGIN  
     ROLLBACK TRANSACTION
 END
ELSE
 IF @@TRANCOUNT >= 1
     COMMIT TRANSACTION
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Thank you...but I'm a bit confused.  If something is "inserted" into the OrderLine table, this is to trigger the Customer's balance being adjusted in the Customer table, right.  So isn't there only one "insert" being the four fields that I have in the OrderLine table (being: OrderNumber, Part number, number_Ordered and price).  It is the Orders table that has the customer number, which in turn links to the Customer table's customer Number.  So you show i.customer_number, but I don't see how there is an i.customer_number.  There is no customer number in the order_line table, so how can there be an i.customer_number.  I am confused as to how to create the trigger on the OrderLine table, to affect a table that isn't directly connected.  Man, I know this sounds confusing!  

no confusion,

with your help reminding me of inconsistencies I can see clearly that this should not be allowed due to referential integrity constraints.


UPDATE C SET C.balance = C.balance +
i.Order_Grand_Total
FROM
Customer as C
inner join
Orders as O
on C.Customer_number = O.Customer_number
inner join
( select SUM(number_ordered*quoted_price) AS Order_Grand_Total, Order_number FROM inserted GROUP BY Order_number ) as i ON
i.Order_number = O.Order_number

--Cheers
CdnGal:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial