?
Solved

Trigger on Insert, Update (SUM of another table)

Posted on 2008-10-29
5
Medium Priority
?
1,490 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

743 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