Problem: writing a triger to check the sum of values in the Percentage column of a table do not exceed 100 % for a given Application Code.

Question: At which stage in a tirgger's life do inserted records (from inserted table) appear in the target table?

Detailed Problem:

CREATE TABLE AppCostCentres
(
ApplicationCode CHAR(8),
SAPCostCentre CHAR(10),
Percentage NUMERIC(5,2),
CONSTRAINT pk_AppCostCentres PRIMARY KEY NONCLUSTERED (ApplicationCode, SAPCostCentre)
)

sample data

AppCode,COstCentre,Percentage
-------------------------------
a,1,80.0
a,2,20.0
b,1,60.0
b,4,40.0


I have the following trigger which fires on insert / update

CREATE TRIGGER trigger_Check_AppCostCentres_Percentage
ON AppCostCentres
FOR INSERT, UPDATE
AS
-- This trigger has been created to check that the Percentages for each Application Code do not
-- exceed 100 %. The transaction is rolled back if this is the case.
DECLARE @Total NUMERIC(5,2)
-- Get the Total percentage for this application Code
SELECT @Total = SUM(a.Percentage)
FROM AppCostCentres a, Inserted i
WHERE a.ApplicationCode = i.ApplicationCode
-- test that sum is not > 100%
IF (@Total > 100.0)
-- Display Error and then Rollback transaction
BEGIN
RAISERROR ('Sum of Percentages for Application exceeds 100 Percent', 16, 1)
ROLLBACK TRANSACTION
END

The trigger works fine if I do insert/ updates one at a time. However if I do bulk insert such as

INSERT AppCostCentres
SELECT *
FROM TableInsert

where Table Insert contains data
AppCode,COstCentre,Percentage
-------------------------------
x,1,30.0
x,2,40.0
x,3,30.0

Before the insert there is no data in the target table (AppCostCentres) for Application x, but the trigger displays the trigger defined insert fails message ('Sum of Percentages for Application exceeds 100 Percent&#39.

If I debug the trigger then the value for @total variable is 300 - I guess this is because there are 3 records to insert, therefore 3 records in the inserted table. However, I would still expect my trigger to work as there shouldn't be a value in the AppCostCentres table until after commit - so the join condition

WHERE a.ApplicationCode = i.ApplicationCode

will not be satisfied and the @total will be less than 100%.

Apologies for the long winded question but any ideas?


Thanks in advance

Neill