Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2008-10-29
8
Medium Priority
?
465 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 61

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 61

Accepted Solution

by:
Kevin Cross earned 1000 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 61

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 61

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 61

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

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

572 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