Table Track Changes Audit - MS SQL

Tracking the changes from a DELETE or UPDATE statement at the table level by creating a compatible [audit].[TableToTrack] and then attaching an after DELETE, UPDATE trigger on the watched table to keep a record of all changes

Example Table [dbo].[TableToTrack]

CREATE TABLE [dbo].[TableToTrack](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Description] [nvarchar](100) NOT NULL,
 CONSTRAINT [PK_TableToTrack] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Audit Table

CREATE TABLE [audit].[TableToTrack](
	[Id] [int] NOT NULL,
	[Description] [nvarchar](100) NOT NULL
) ON [PRIMARY]
GO

Add Database Trigger to track the table changes

Key point here is an UPDATE is tracked as the trigger having data in the special row variables for the trigger statement insert and deleted.  If you think about it makes sense as an update can be thought of as the deletion of the current record and an insertion of the new value based off a copy of the record to be deleted

-- Assumption of [audit] schema with a compatible table
CREATE TRIGGER [dbo].[TrackChanges]
   ON  [dbo].[TableToTrack]
   AFTER DELETE, UPDATE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	-- update
	IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
	BEGIN
		INSERT into [audit].[TableToTrack] ([Id], [Description])
		SELECT [Id], [Description] FROM inserted i; 
	END

	-- delete
	IF EXISTS (SELECT * FROM deleted) AND NOT EXISTS(SELECT * FROM inserted)
	BEGIN
		INSERT into [audit].[TableToTrack] ([Id], [Description])
		SELECT [Id], [Description] FROM deleted d; 
	END
END