mbaldock avatar image
mbaldock asked

trigger to update row value based on other rows

hello all, we have procedures that staff use to periodically recalculate certain rows. we have different procedures for each of our (10) main tables. the problem is that our users frequently forget to run the procedures. i'm wondering if i can put the following procedure into a trigger to maintain data integrity? as an example, this is one of the procedures (code modified here for simplicity): 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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

Kev Riley avatar image
Kev Riley answered
Personally I don't like having business logic hidden away in triggers. Actually to be honest I don't like triggers much at all. I'd be tempted to steer you in other directions... When the data in these tables is updated, is it done through other stored procedures? If so why not do the updates then? Does this data need to be persisted? Can it not be worked out on-the-fly through using views?
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

mbaldock avatar image mbaldock commented ·
@Kev Riley: thanks for the response. I agree with you on not hiding business logic. Data can be updated with other procedures, but also manually, hence my problem. I had not thought about using a view at all. i think a view might just do the trick... i have a feeling this calculated row is sometimes supplied and sometimes calculated! i'll have the verify the business rules.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.