-- Write an insert, delete and updated trigger named
-- trgLineUpdate for the Line Table. Update the Invoice
-- table to show the total value of the invoice (the sum
-- of the Line table Line_Units times Line_Price).
You can create the trigger a little bit simpler and more clear.
You can write the trigger like this:
Hope I wrote the trigger correctly and don't have a typo there as I'm not on my PC and it's written in Notepad.
The CTE calculates SUM difference.. If the record was inserted you wil receive sum of the inserted records for invoices. If records were deleted you will receive sum of the deleted records as negative value. If records were modified you will receive the SUM of differences.. Sum of new values - sum of old values.. Then when joined to the infoce the, original amount is updated by the difference. This helps you that you do not need to read the lines table again as you have all the diff data in the INSERTED and DELETED virtual tables.
When the trigger is written in this way, it will also keep the invoice table consistent, even you write a query, which inserts/deletes/updates lines from several invoices in single query. The triger updates amounts for all the invoices affected.
Anyway I thinkg and a stored proc for adding, removing or modifying rows from the invoce could be better and the sums can be calculated in the stored proc. But if you require the trigger, the one I post here, could be a way.
EDIT - Sample
Any chance you can tell us what the problem is please? What happens with this and what do you actually want to happen? Have you tried the trigger for just one of update, insert delete, just to see if that does what you want in one case? Have you read BOL for creating triggers? [http://msdn.microsoft.com/en-us/library/ms189799.aspx]: http://msdn.microsoft.com/en-us/library/ms189799.aspx
answered Dec 04, 2010 at 05:17 AM