Invoice Total Trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Invoice Total Trigger

Author

Topic

steve_c
Yak Posting Veteran

54 Posts

Posted - 2007-10-23 : 08:05:02

Hi all - need to create a trigger which updates an invoicetotal field in my invoices table. My tables are as follows:

Invoices
invoiceid
vatcodeid
invoicetotal (decimal 19,2)

InvoiceLines
invoicelineid
invoiceid
qty (int)
amount (decimal)

vatcodes
vatcodeid
vatpercent (float)

basically, on an insert of an invoiceline, I would like to update the invoicetotal (qty * amount) * vatpercent.

Am having trouble working out how to do this as I'm very new to triggers and have trouble getting my head around how to do the joins etc...

Would be very grateful for any help :)

Stephen.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-23 : 08:34:50

I can't see why you need a trigger for this.
Make InvoiceTotal a persisted computed column and calculate the sum you want.

E 12°55'05.25"
N 56°04'39.16"

steve_c
Yak Posting Veteran

54 Posts

Posted - 2007-10-23 : 08:42:47

I am thinking of overheads. Down the line, I will need to generate reports of client turnover for a given time. If I do not make use of a trigger won't I have to do huge calculations on 100s of qty*amount on 1000s of invoices?

Kristen
Test

22859 Posts

Posted - 2007-10-23 : 09:21:39

Its probably six-of-one and half-a-dozen-of-the-other.

Personally I would have a real column on the InvoiceHeader and keep it accurate by having a trigger on the OrderItems. Every table I have a computed column on comes back to haunt me with arith-abort , grief trying to insert into VIEWs and a bunch of other "special requirements"

Haven't review it, but the trigger should be something like this:


UPDATE U
SET InvoiceTotal = InvoiceTotal + X.[Adjust]
FROM Invoices AS U
	JOIN 
(
	SELECT	[InvoiceID] = COALESCE(I.InvoiceID, D.InvoiceID),
		[Adjust] = SUM(COALESCE(((I.qty * I.amount) * I.vatpercent), 0) 
			- COALESCE(((D.qty * D.amount) * D.vatpercent), 0))
	FROM	inserted I
		FULL OUTER JOIN deleted D
			ON  D.InvoiceID= I.InvoiceID
			AND D.InvoiceItem = I.InvoiceItem
	GROUP BY InvoiceID
) AS X
		ON X.InvoiceID = U.InvoiceID

Kristen

steve_c
Yak Posting Veteran

54 Posts

Posted - 2007-10-23 : 10:53:05

That's perfect - thanks very much Kristen :)

Kristen
Test

22859 Posts

Posted - 2007-10-23 : 12:22:48

needs to be a

FOR INSERT, UPDATE, DELETE

type trigger - to catch all three types of amendment. Probably obvious ... but just in case not!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources