SQL Stored Proc: Help converting to trigger

Question

Hi all,

I'd like to convert a stored proc into a trigger. The stored proc is supposed to be run after inserts/updates, but users forget to execute it more often than not!

The stored proc inserts recalculated values for some rows:

--Delete rows where RowCode=111
DELETE FROM dbo.TableA WHERE [year]>=1998 AND RowCode=111

--Insert new values for RowCode=111 for years>=1998
INSERT INTO dbo.TableA
SELECT [Year], RowCode=111, ColCode, SUM(Amt) AS Amt
FROM dbo.TableA
WHERE [Year]>=1998 AND RowCode IN (1,12,23) AND ColCode=14
GROUP BY [Year], ColCode

I'd like to put this into a trigger so that the users don't have to think about running a procedure.

I have the following started, but I'm not sure if i'm on the right track. Should I be using FOR, AFTER or INSTEAD OF?

CREATE TRIGGER TRIU_TableA 
ON TableA 
FOR INSERT,UPDATE AS

BEGIN
  SET NOCOUNT ON
  IF EXISTS (SELECT * FROM Inserted WHERE RowCode=111)
  BEGIN
    INSERT INTO TableA
    SELECT [Year], RowCode, ColCode, SUM(Amt) AS Amt
    FROM Inserted
    WHERE [Year]>=1998 AND RowCode IN (1,12,23) AND ColCode=14
    GROUP BY [Year], ColCode
  END

END

Any help on how to go about this would be very much appreciated.

Many thanks
Mark

Answer 1

You should be using FOR

Comparing the Trigger to the Stored Proc it all looks fine.

What other problems do you have?

Answer 2

You forgot the delete part.

But why doesn't your application just run the existing proc automatically after every insert/update? Are people updating your database from a query window?

Make it an AFter trigger, After and For are the same, but after is the newer syntax.