Solved

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

Posted on 2008-10-29
8
423 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Help 27 46
SQL SERVER 2008 R2 Could not obtain information about Windows NT group/user 5 36
Loops and updating in SQL Query 9 33
SQL Count issue 24 17
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…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

821 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