SQL Stored Proc: Help converting to trigger

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 
O开发者_运维百科N 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

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.

You should be using FOR

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

What other problems do you have?