I use that for all status (update, insert and delete)
CREATE TRIGGER trg_Insert_Test
ON [dbo].[MyTable]
AFTER UPDATE, INSERT, DELETE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Activity NVARCHAR (50)
-- update
IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
BEGIN
SET @Activity = 'UPDATE'
END
-- insert
IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS(SELECT * FROM deleted)
BEGIN
SET @Activity = 'INSERT'
END
-- delete
IF EXISTS (SELECT * FROM deleted) AND NOT EXISTS(SELECT * FROM inserted)
BEGIN
SET @Activity = 'DELETE'
END
-- delete temp table
IF OBJECT_ID('tempdb..#tmpTbl') IS NOT NULL DROP TABLE #tmpTbl
-- get last 1 row
SELECT * INTO #tmpTbl FROM (SELECT TOP 1 * FROM (SELECT * FROM inserted
UNION
SELECT * FROM deleted
) AS A ORDER BY A.Date DESC
) AS T
-- try catch
BEGIN TRY
INSERT INTO MyTable (
[Code]
,[Name]
.....
,[Activity])
SELECT [Code]
,[Name]
,@Activity
FROM #tmpTbl
END TRY BEGIN CATCH END CATCH
-- delete temp table
IF OBJECT_ID('tempdb..#tmpTbl') IS NOT NULL DROP TABLE #tmpTbl
SET NOCOUNT OFF;
END