Yes indeed, here's what I ended up going with.
There is probably a more effecient way to do this, but the hope is that it never gets triggered
-
-- Insert statements for trigger here
-
DECLARE @WhatType as varchar(20)
-
DECLARE @WhoDidIt as varchar(100)
-
DECLARE @Notes as varchar(500)
-
DECLARE @Check1 as int
-
DECLARE @Check2 as int
-
-
SET @WhoDidIt = system_user
-
SET @Notes='Testing'
-
-
SELECT @Check1 = (SELECT COUNT(*) FROM Inserted)
-
SELECT @Check2 = (SELECT COUNT(*) FROM Deleted)
-
-
if(@Check1>0 AND @Check2>0)
-
BEGIN
-
--isUpdate
-
Set @WhatType='Update'
-
SELECT @Notes='Delete: '+(SELECT SomeColumn FROM Deleted)
-
Set @Notes=@Notes+ 'Insert: '+(SELECT SomeColumn FROM Inserted)
-
END
-
ELSE
-
if(@Check1>0 AND @Check2=0)
-
BEGIN
-
--isInsert
-
Set @WhatType='Insert'
-
SELECT @Notes='Insert: '+(SELECT SomeColumn FROM Inserted)
-
END
-
ELSE
-
if(@Check1=0 AND @Check2>0)
-
BEGIN
-
--isDelete
-
Set @WhatType='Delete'
-
SELECT @Notes='Delete: '+(SELECT SomeColumn FROM Deleted)
-
END
-
ELSE
-
BEGIN
-
Set @WhatType='Unknown'
-
END
-
-
INSERT INTO [ChangesTracking]
-
(
-
[Occured]
-
,[WhoDidIt]
-
,[TableAffected]
-
,[Type]
-
,[Notes]
-
)
-
VALUES
-
(
-
getdate()
-
,@WhoDidIt
-
,'MyTableName'
-
,@WhatType
-
,@Notes
-
)
-
