Trigger to updated default properties result in nesting level of 32 [duplicate]

I have SQL Server which is linked to MS Access through a DSN link:

Read more

Where SQL Server works as the developer back-end and MS Access works as the user front-end.

In the table "Components", I have two columns which are supposed to be system generated:

• UpdatedAt: GETDATE()
• UpdatedBy: SUSER_SNAME()

The are default filled out by the following constraints:

ALTER TABLE [dbo].[Components] ADD CONSTRAINT [DF_Components_UpdatedAt]  DEFAULT (getdate()) FOR [UpdatedAt]

ALTER TABLE [dbo].[Components] ADD CONSTRAINT [DF_Components_UpdatedBy]  DEFAULT (SUSER_SNAME()) FOR [UpdatedBy]

Both of these works as intended.

But now I have some trouble configurating the trigger, which will update "UpdatedAt", "UpdatedBy" after a row/record are updated.

So far I have the Trigger showed below. However in MS access I have several cases of Me.Refresh in a form (primarily used to update the row/record, when a column included in a computed column are updated, such that the computed column are evaluated right away. Apparently the trigger combined with Me.Refresh results in an 'endless' loop of them triggering each other and them selves, until SQL Server reaches nesting level of 32:

https://learn.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-ver16&redirectedfrom=MSDN

CREATE TRIGGER [dbo].[TR_Components_UpdateDefaultProperties]
ON [dbo].[Components]
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE dbo.[Components]
    SET UpdatedAt = GETDATE(),
        UpdatedBy = SUSER_SNAME()
    FROM inserted i
    WHERE dbo.[Components].ComponentNumber = i.ComponentNumber;
END;

I found a lot of related questions and great answers here on Stackoverflow which has helped me to this point, but haven't yet found one solving my specific problem.

What could be a work around to avoid the loop of nesting?

The ComputedColumn looks like this:

[ComputedColumn]  AS (CONVERT([varchar](1),[Prefix])+CONVERT([varchar](3),[SequenceNumber])) PERSISTED

And here is the VB in MS Access

Private Sub UpdateComputedColumn()
    If Not IsNull(Me.Prefix) And Not IsNull(Me.SequenceNumber) Then
        Me.Refresh
    End If
End Sub

Private Sub Prefix_AfterUpdate()
    Call UpdateComputedColumn
End Sub

Private Sub SequenceNumber_AfterUpdate()
    Call UpdateComputedColumn
End Sub

Answer

Your trigger updates the table that the trigger is on, and then updates it again, and again... and again i.e. your trigger has an infinite recursion loop because each time it updates it fires the trigger again. You have to find a way to identify that the system update has happened and bail without repeating.

Give these are system generated columns I would suggest using the UPDATE function to detect this by adding if update(UpdatedAt) return; at the top of the trigger i.e. if the UpdatedAt column was updated, it means it was the trigger, so you don't want to run again.

CREATE TRIGGER [dbo].[TR_Components_UpdateDefaultProperties]
ON [dbo].[Components]
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    -- Prevent recursion
    IF UPDATE(UpdatedAt) RETURN;

    UPDATE dbo.[Components]
    SET UpdatedAt = GETDATE(),
        UpdatedBy = SUSER_SNAME()
    FROM inserted i
    WHERE dbo.[Components].ComponentNumber = i.ComponentNumber;
END;