Using Trigger to Log Row Insert and Delete

Log inserts and deletes of a table to a log table.

CREATE TRIGGER [dbo].[tr_tbTable01_Insert_Delete] ON [dbo].[tbTable01]
AFTER
	INSERT, 
	DELETE
AS

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET NOCOUNT ON;

BEGIN;

	DECLARE @InsertDateTime DATETIME2(7) = SYSDATETIME()
	DECLARE @InsertedCount INT = (SELECT COUNT(*) FROM inserted)
	DECLARE @DeletedCount INT = (SELECT COUNT(*) FROM deleted)

	INSERT INTO tbLog
	(		
		Message,
		RowCount,
		CreatedDate
	)
	SELECT	
		'Inserted row from tbTable01'			
		,@InsertedCount
		,@InsertDateTime
	FROM	
		inserted 

	UNION

	SELECT	
		'Delete row from tbTable01'			 
		,@DeletedCount
		,@InsertDateTime
	FROM	
		deleted 
  
END;
GO

ALTER TABLE [dbo].[tbTable01] ENABLE TRIGGER [tr_tbTable01_Insert_Delete]