Solved

Trigger on Insert, Update (SUM of another table with WHERE Clause)

Posted on 2008-10-29
8
437 Views
Last Modified: 2011-08-18
Following a similar question recently raised

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                         9
2                           1                           90.00               5.00                       3                         6
3                           1                           10.00               1.00                       5                         6
4                           1                            11.00              1.00                       8                         9

I also have another top level table called Quotes

quot_orderquoteid      quot_labourcost   quote_cashback
1                                 NULL                     NULL

I need that when an row is inserted or updated in the QuoteItems table that the sum of the quit_listprice  is added to the Quote.quot_labourcost field where the quit_productfamilyid = '9' and the quot_cashback field is summed listprice where the quit_productfamilyid = '6'

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
  • 5
  • 3
8 Comments
 

Author Comment

by:MimUK
ID: 22830748
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22830936
Something like this in a trigger.
CREATE TRIGGER dbo.trig_QuoteItems
   ON  dbo.QuoteItems
   AFTER INSERT,UPDATE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
    -- Insert statements for trigger here
	UPDATE tblTo
	SET tblTo.quot_labourcost = tblFrom.quot_labourcost 
	, tblTo.quote_cashback = tblFrom.quote_cashback
	FROM quotes tblTo INNER JOIN
	(SELECT q.quit_orderquoteid
	, sum(case q.quit_productfamilyid when '9' then q.quit_listprice else 0.00 end) as quot_labourcost
	, sum(case q.quit_productfamilyid when '6' then q.quit_listprice else 0.00 end) as quote_cashback
	FROM quoteitems q INNER JOIN inserted i
	ON i.quit_orderquoteid = q.quit_orderquoteid
	GROUP BY q.quit_orderquoteid) tblFrom
	ON tblTo.quot_orderquoteid = tblFrom.quit_orderquoteid
END
GO

Open in new window

0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 250 total points
ID: 22830978
And sorry, I forgot the quit_deleted part. ;)  I see what you were doing with that now MimUK.
CREATE TRIGGER dbo.trig_QuoteItems
   ON  dbo.QuoteItems
   AFTER INSERT,UPDATE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
    -- Insert statements for trigger here
	UPDATE tblTo
	SET tblTo.quot_labourcost = tblFrom.quot_labourcost 
	, tblTo.quote_cashback = tblFrom.quote_cashback
	FROM quotes tblTo INNER JOIN
	(SELECT q.quit_orderquoteid
	, sum(case q.quit_productfamilyid when '9' then q.quit_listprice else 0.00 end) as quot_labourcost
	, sum(case q.quit_productfamilyid when '6' then q.quit_listprice else 0.00 end) as quote_cashback
	FROM quoteitems q INNER JOIN inserted i
	ON i.quit_orderquoteid = q.quit_orderquoteid
	WHERE q.quit_deleted is null
	GROUP BY q.quit_orderquoteid) tblFrom
	ON tblTo.quot_orderquoteid = tblFrom.quit_orderquoteid
END
GO

Open in new window

0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22831029
Solution basics:
+Both INSERT and UPDATE have a table called inserted with the updated/new record.  
+The quote items you want to sum will be identified by inserted tables value for column quit_orderquoteid; however, this table only has the updated/inserted row.
+Given above, must query the quoteitems table based on id found to get sum of other records matching that id and where criteria you want like quit_deleted is null.
+Using case when logic you can get the two different sums in one statement.
+Inner join with quotes table as you could have multiple records being inserted and so multiple quotes could be updated.

Hope that helps.
0
 

Author Comment

by:MimUK
ID: 22831247
Thank you very much mwvisa1 for a thorough answer and explanation.
Really appreciated

However,  I cannot see where I would apply the * quit_quantity

Cheers,

Mim
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22832777
Sorry I didn't see that part in the question.  Must not have had enough coffee. :) Becoming a theme for me these days...

Anyway, that would make perfect sense in a quoteitems table that you have an extended price based on quantity, so should have thought of it.

All you would do is change where you see:
q.quit_listprice
to:
q.quit_listprice * q.quit_quantity
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22832809
I stated where you see and was implying "every" where you see quit_listprice; however, if you only want one or the other of the values to be quantity specific, then you only need to change that one.  But if my guess is right, you probably want to replace both occurrences of listprice with the formula to get extended listprice.
0
 

Author Closing Comment

by:MimUK
ID: 31511147
Thank you very much for your excellent assistance with this.
Much appreciated !!
0

Featured Post

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
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
Viewers will learn how the fundamental information of how to create a table.

726 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