I would like some guidance on SRP and creating trigger(s) for logging data changes.  I am going to use a history or shadow table that mimics the data table and triggers to log the changes.  What are your thoughts and concerns on performance, maintainability, nesting, recusion, etc. with the two options?

In the past, I used one trigger each for INSERT, UPDATE, and DELETE:

CREATE TRIGGER [fact].[DataTable_LogChanges_I]
ON [fact].[DataTable]
AFTER INSERT
AS
BEGIN

	SET NOCOUNT ON;

	INSERT INTO [fact].[Shadow_DataTable]([Action], [SubmissionId], [TimeBeg], [TimeEnd], [Error_Count], [Active])
	SELECT 'I', i.[SubmissionId], i.[TimeBeg], i.[TimeEnd], i.[Error_Count], i.[Active]
	FROM INSERTED i;

END;
GO

CREATE TRIGGER [fact].[DataTable_LogChanges_U]
ON [fact].[DataTable]
AFTER UPDATE
AS
BEGIN

	SET NOCOUNT ON;

	UPDATE [fact].[DataTable]
	SET	[tsModified]		= sysdatetimeoffset(),
		[tsModifiedHost]	= host_name(),
		[tsModifiedUser]	= suser_sname(),
		[tsModifiedApp]		= app_name()
	FROM INSERTED
	WHERE	[fact].[DataTable].[SubmissionId]	= INSERTED.[SubmissionId];

	INSERT INTO [fact].[Shadow_DataTable]([Action], [SubmissionId], [TimeBeg], [TimeEnd], [Error_Count], [Active])
	SELECT 'U', i.[SubmissionId], i.[TimeBeg], i.[TimeEnd], i.[Error_Count], i.[Active]
	FROM INSERTED i;

END;
GO

CREATE TRIGGER [fact].[DataTable_LogChanges_D]
ON [fact].[DataTable]
AFTER DELETE
AS
BEGIN

	SET NOCOUNT ON;

	INSERT INTO [fact].[Shadow_DataTable]([Action], [SubmissionId], [TimeBeg], [TimeEnd], [Error_Count], [Active])
	SELECT 'D', i.[SubmissionId], i.[TimeBeg], i.[TimeEnd], i.[Error_Count], i.[Active]
	FROM DELETED i;

END;
GO

But now my thought process is one trigger for logging (other triggers, if necessary, will be written for the purpose without changing the log trigger):

CREATE TRIGGER [fact].[DataTable_LogChanges]
ON [fact].[DataTable]
AFTER INSERT, UPDATE, DELETE
AS
BEGIN

	SET NOCOUNT ON;

	DECLARE @Action	CHAR(1);
	DECLARE @Insert	BIT = 0;
	DECLARE @Delete	BIT = 0;

	IF EXISTS (SELECT TOP 1 1 FROM INSERTED)	SET @Insert = 1;
	IF EXISTS (SELECT TOP 1 1 FROM DELETED)		SET @Delete = 1;

	IF (@Insert = 1 AND @Delete = 0)	SET @Action = 'I';
	IF (@Insert = 1 AND @Delete = 1)	SET @Action = 'U';
	IF (@Insert = 0 AND @Delete = 1)	SET @Action = 'D';

	IF (@Action = 'U')
	UPDATE [fact].[DataTable]
	SET	[tsModified]		= sysdatetimeoffset(),
		[tsModifiedHost]	= host_name(),
		[tsModifiedUser]	= suser_sname(),
		[tsModifiedApp]		= app_name()
	FROM INSERTED
	WHERE	[fact].[DataTable].[SubmissionId]	= INSERTED.[SubmissionId];

	IF (@Action IN ('I', 'U'))
	INSERT INTO [fact].[Shadow_DataTable]([Action], [SubmissionId], [TimeBeg], [TimeEnd], [Error_Count], [Active])
	SELECT @Action, i.[SubmissionId], i.[TimeBeg], i.[TimeEnd], i.[Error_Count], i.[Active]
	FROM INSERTED i;

	IF (@Action = 'D')
	INSERT INTO [fact].[Shadow_DataTable]([Action], [SubmissionId], [TimeBeg], [TimeEnd], [Error_Count], [Active])
	SELECT @Action, d.[SubmissionId], d.[TimeBeg], d.[TimeEnd], d.[Error_Count], d.[Active]
	FROM DELETED d;

END;
GO

Hi Rogge,

I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated.
Thank you for your understanding and support.

Regards,
Fanny Liu