asked on

triggers with multiple tables

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!

miron

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

miron

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

ASKER CERTIFIED SOLUTION

miron

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

CdnGal

ASKER

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!

miron

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

CleanupPing

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.