-
Trigger problem on SQL 2000
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'.
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|