ALTER PROCEDURE spTable1_T (@year int, @cntry int) AS BEGIN -- Calculate ratio: rowcode 3=(rowcode 5/rowcode 1)*100 -- UPDATE Table1 SET Amount=( ((SELECT d.Amount FROM Table1 d WHERE d.Rowcode=5 AND d.Colcode=1140 AND d.[year]=@year AND d.countrycode=@cntry) / (SELECT d1.Amount FROM Table1 d1 WHERE d1.Rowcode=1 AND d1.Colcode=1140 AND d1.[year]=@year AND d1.Countrycode=@cntry) )*100) WHERE RowCode=3 AND ColCode=1140 AND [year]=@year AND countrycode=@cntry ; END Here is an attempt at writing the above procedure as a trigger:
ALTER TRIGGER [dbo].[Tiu_Table1] ON [dbo].[Table1] AFTER INSERT, UPDATE AS BEGIN SET NOCOUNT ON ; IF EXISTS(SELECT * FROM inserted WHERE Rowcode IN (1,5) AND Colcode=1140) /* If rows used in calculation have changed, recalculate the ratio */ MERGE INTO Table1 AS Tgt USING (SELECT Year, Rowcode=3, Colcode, CountryCode, Amount = ((SELECT Amount FROM inserted i1 WHERE i1.Rowcode=5 AND i1.Colcode=1140) / (SELECT Amount FROM inserted i2 WHERE i2.Rowcode=1 AND i2.Colcode=1140))*100 FROM inserted WHERE Rowcode IN (1,5) AND Colcode=1140 ) AS Sce ON Tgt.Year=Sce.Year AND Tgt.Colcode=Sce.Colcode AND Tgt.CountryCode=Sce.CountryCode AND Tgt.Rowcode=3 WHEN MATCHED THEN UPDATE SET Amount=Sce.Amount, ModifyDate=GETDATE() WHEN NOT MATCHED THEN INSERT (Year, Rowcode, Colcode, CountryCode, Amount) VALUES (Sce.Year, Sce.Rowcode, Sce.Colcode, Sce.CountryCode, Sce.Amount); END The problem I have is when only ONE of the rows used in calculating the ratio is updated, the result is NULL. Can anyone point me in the right direction? Can this be done in a trigger or have a made a mistake somewhere? Many thanks for your help.
