Solved

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

Posted on 2008-10-29
8
404 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
  • 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
 
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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

746 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

10 Experts available now in Live!

Get 1:1 Help Now