Solved

Trigger on Insert, Update (SUM of another table)

Posted on 2008-10-29
5
1,474 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

680 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