question

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][1] [1]: http://msdn.microsoft.com/en-us/library/ms189799.aspx

You can create the trigger a little bit simpler and more clear. You can write the trigger like this: CREATE TRIGGER trgLineUpdate ON LINE AFTER INSERT, DELETE, UPDATE NOT FOR REPLICATION AS WITH DiferenceSums AS ( SELECT ISNULL(I.IVN_NUMBER, D.INV_NUMBER) AS INV_NUMBER, SUM(ISNULL(I.LINE_UNITS, 0) * ISNULL(I.LINE_PRICE, 0) - ISNULL(D.LINE_UNITS, 0) * ISNULL(D.LINE_PRICE, 0)) AS INV_SUM FROM INSERTED I FULL OUTER JOIN DELETED D ON I.INV_NUMBER = D.INV_NUMBER GROUP BY ISNULL(I.INV_NUMBER, D.INV_NUMBER) ) UPDATE I SET AMOUNT = ISNULL(AMOUNT, 0) + DF.INV_SUM FROM INVOICE I INNER JOIN DifferenceSums DF ON I.INV_NUMBER = DF.INV_NUMBER GO 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** CREATE TABLE dbo.Invoice ( INV_NUMBER int NOT NULL IDENTITY(1,1), INV_DATE datetime NOT NULL, AMOUNT money, CONSTRAINT PK_Invoice PRIMARY KEY CLUSTERED (INV_NUMBER) ) GO CREATE TABLE dbo.Lines ( LINE_ID int NOT NULL IDENTITY(1,1), INV_NUMBER int NOT NULL, LINE_UNITS int NOT NULL, LINE_PRICE money NOT NULL, CONSTRAINT PK_Lines PRIMARY KEY CLUSTERED (LINE_ID), CONSTRAINT FK_Lines_Invoice FOREIGN KEY (INV_NUMBER) REFERENCES dbo.Invoice(INV_NUMBER) ON UPDATE CASCADE ON DELETE CASCADE ) GO CREATE TRIGGER dbo.trgLineUpdate ON dbo.Lines AFTER INSERT, DELETE, UPDATE NOT FOR REPLICATION AS WITH DiferenceSums AS ( SELECT ISNULL(I.INV_NUMBER, D.INV_NUMBER) AS INV_NUMBER, SUM(ISNULL(I.LINE_UNITS, 0) * ISNULL(I.LINE_PRICE, 0) - ISNULL(D.LINE_UNITS, 0) * ISNULL(D.LINE_PRICE, 0)) AS INV_SUM FROM INSERTED I FULL OUTER JOIN DELETED D ON I.INV_NUMBER = D.INV_NUMBER GROUP BY ISNULL(I.INV_NUMBER, D.INV_NUMBER) ) UPDATE I SET AMOUNT = ISNULL(AMOUNT, 0) + DF.INV_SUM FROM INVOICE I INNER JOIN DiferenceSums DF ON I.INV_NUMBER = DF.INV_NUMBER GO DECLARE @inv_number int DECLARE @line_number int INSERT INTO dbo.Invoice(INV_DATE) VALUES (GETDATE()) SELECT @inv_number = SCOPE_IDENTITY() SELECT * FROM dbo.Invoice INSERT INTO dbo.Lines(INV_NUMBER, LINE_UNITS, LINE_PRICE) VALUES(@inv_number, 5, 15) SELECT * FROM dbo.Invoice INSERT INTO dbo.Lines(INV_NUMBER, LINE_UNITS, LINE_PRICE) VALUES (@inv_number, 10, 5) SELECT @line_number = SCOPE_IDENTITY() SELECT * FROM dbo.Invoice UPDATE dbo.Lines SET LINE_UNITS = 5 WHERE LINE_ID = @line_number SELECT * FROM dbo.Invoice DELETE dbo.Lines WHERE LINE_ID = @line_number SELECT * FROM dbo.Invoice