Solved

Trigger on Insert, Update (SUM of another table)

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

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

732 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