Solved

Trigger on Insert, Update (SUM of another table)

Posted on 2008-10-29
5
1,457 Views
Last Modified: 2008-10-29
I have a table called quoteitems
that has product items and prices inserted.

quit_lineitemid     quit_orderquoteid   quit_listprice  quit_commission    quit_productid    quit_productfamilyid

1                           1                           100.00            15.00                      4                         3
2                           1                           90.00               5.00                       3                         2

I also have another top level table called Quotes

quot_orderquoteid      quote_suppliercommission
1                                 NULL

I need that when an row is insertedor updated in the QuoteItems table that the sum of the quit_commission is added to the Quote.quot_suppliercommission field.
They are joined by the quot and quit_orderquoteid and I only need to sum where the quit_deleted is null.

Cheers, help is appreciated

Mim





0
Comment
Question by:MimUK
  • 4
5 Comments
 

Author Comment

by:MimUK
ID: 22830172
I think I'v solved it.
Just need to exclude deleted ones now
CREATE trigger [trg_QuoteCommission] on [dbo].[QuoteItems]

for Insert,Update

as

UPDATE Quotes

SET quot_suppliercommission = t.quit_commission

FROM 

(Select quit_orderquoteid, SUM(quit_commission) quit_commission

FROM QuoteItems

GROUP BY quit_orderquoteid ) t

INNER JOIN Quotes

ON Quotes.quot_OrderQuoteID = t.quit_orderquoteid

Open in new window

0
 

Accepted Solution

by:
MimUK earned 0 total points
ID: 22830192
as
UPDATE Quotes
SET quot_suppliercommission = t.quit_commission
FROM
(Select quit_orderquoteid, SUM(quit_commission) quit_commission
FROM QuoteItems where quit_deleted is null
GROUP BY quit_orderquoteid ) t
INNER JOIN Quotes
ON Quotes.quot_OrderQuoteID = t.quit_orderquoteid

0
 
LVL 5

Expert Comment

by:Cvijo123
ID: 22830208
here is triggers that u can do but i would suggest 2 other aproach to achive what u need becouse triggers are used when other methods cant work.

1. Suggestion
If u need to get sum of suppliercommission then use single select to get them grouped by quot_orderquoteid
so your SP or select would be simular to this:

Select
 quot_orderquoteid,
 sum( quote_suppliercommission ) as quote_suppliercommission
from quoteitems
-- use where if u need here to filter something
group by quot_orderquoteid


2. Suggestion
If you are using SP to update, insert or delete your quoteitems data then put additional insert, updates in your SP to generete those sum's instead of using triggers. So after u insert item in quoteitems use another update in same SP to calculate suppliercommission in your Quotes table.


but if u need to stick with triggeres something like this should work:



-- insert

CREATE TRIGGER trgQuoteitems_Insert ON dbo.quoteitems 

FOR INSERT

AS
 

INSERT INTO Quotes (quot_orderquoteid, quote_suppliercommission ) 

SELECT quot_orderquoteid, sum(quit_commission) 

FROM quoteitems

inner join inserted on quoteitems.quot_orderquoteid = inserted.quot_orderquoteid

group by quot_orderquoteid
 
 
 
 

-- update

CREATE TRIGGER trgQuoteitems_Insert ON dbo.quoteitems 

FOR UPDATE

AS
 

UPDATE Quotes 

SET Quotes.quote_suppliercommission = sum( quoteitems.quote_suppliercommission )

FROM quoteitems

inner join inserted on quoteitems.quot_orderquoteid = inserted.quot_orderquoteid

group by quot_orderquoteid

Open in new window

0
 

Author Comment

by:MimUK
ID: 22830368
I think I follow you.

The question I have now is that I need to also sum other values and preferably within the same trigger..... these are based on product families

SET quot_labourcost = t.quit_listprice
FROM
(Select quit_orderquoteid, SUM(quit_listprice) quit_listprice
FROM QuoteItems WHERE quit_deleted is null and quit_productfamilyid = '9'
GROUP BY quit_orderquoteid ) t
INNER JOIN Quotes
ON Quotes.quot_OrderQuoteID = t.quit_orderquoteid


and

SET quot_cashback = t.quit_listprice
FROM
(Select quit_orderquoteid, SUM(quit_listprice) quit_listprice
FROM QuoteItems WHERE quit_deleted is null and quit_productfamilyid = '6'
GROUP BY quit_orderquoteid ) t
INNER JOIN Quotes
ON Quotes.quot_OrderQuoteID = t.quit_orderquoteid
0
 

Author Comment

by:MimUK
ID: 22830468
Hi Cvijo123

I also get an error 157: An aggregate may not appear on the set list of an update statement
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now