SQL server using computed column and user defined function to grab datetime based on change in another column

Question

Given: Given a Microsoft SQL (2016 and above) database table Log with multiple columns including these important ones: id (primary key), code (an integer that can take multiple values representing status changes), lastupdated (a datetime field)...

What I need: I need to add a computed column ActiveDate which stores the exact first time when the code changed to 10 (i.e. an active status). As the status keep[s changing in future, this column must maintain the same value as the exact time it went active (thus keeping the active datetime record persistently). This timestamp value should initially begin with a NULL.

My approach I want the activedate field to automatically store the datetime at which the status code becomes 10, but when the status changes again, I want it to remain the same. Since I can't reference a calculated column from a calculated column, I created a user defined function to fetch the current value of activedate and use that whenever the status code is not 10.

Limitations:

• I can't make modifications to the Db or to columns (other than the new columns I can add).
• This T-SQL script must be idempotent such that it can be run multiple times at anytime in the production pipeline without losing or damaging data.

Here is what I tried.

IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name=N'ActiveDate' AND OBJECT_ID = OBJECT_ID(N'[dbo].[Log]'))
    /* First, create a dummy ActiveDate column since the user-defined function below needs it */
    ALTER TABLE [dbo].[Log] ADD ActiveDate DATETIME NULL

    IF OBJECT_ID('UDF_GetActiveDate', 'FN') IS NOT NULL
       DROP FUNCTION UDF_GetActiveDate
    GO

    /* Function to grab the datetime when status goes active, otherwise leave it unchanged */ 
    CREATE FUNCTION UDF_GetActiveDate(@ID INT, @code INT) RETURNS DATETIME WITH SCHEMABINDING AS
        BEGIN
           DECLARE @statusDate DATETIME
           SELECT @statusDate = CASE
              WHEN (@code = 10) THEN [lastupdated]
              ELSE (SELECT [ActiveDate] from [dbo].[Log] WHERE id=@ID)
           END
           FROM [dbo].[Log] WHERE id=@ID
           RETURN @statusDate
        END
    GO
    
    /* Rename the dummy ActiveDate column so that we can be allowed to create the computed one */
    EXEC sp_rename '[dbo].[Log].ActiveDate', 'ActiveDateTemp', 'COLUMN';

    /* Computed column for ActiveDate */
    ALTER TABLE [dbo].[Log] ADD ActiveDate AS (
       [dbo].UDF_GetActiveDate([id],[code])
    ) PERSISTED NOT NULL

    /* Delete the dummy ActiveDate column */
    ALTER TABLE [dbo].[Log] DROP COLUMN ActiveDateTemp;

    print ('Successfully added ActiveDate column to Log table')
GO

What I get: The following errors

• [dbo].[Log].ActiveDate cannot be renamed because the object participates in enforced dependencies.
• Column names in each table must be unique. Column name 'ActiveDate' in table 'dbo.Log' is specified more than once.

Is my approach wrong? Or is there a better way to achieve the same result? Please help.

Answer 1

You shouldn't try to compute a column from itself.

Instead, I'd use a trigger...

CREATE TRIGGER dbo.log__set_active_date
ON dbo.log
AFTER INSERT, UPDATE
AS 
BEGIN
  SET NOCOUNT ON;

  UPDATE
    log
  SET
    active_date = INSERTED.last_updated
  FROM
    dbo.log
  INNER JOIN
    INSERTED
      ON log.id = INSERTED.id
  WHERE
        INSERTED.code    = 10
    AND log.active_date IS NULL  -- Added to ensure the value is only ever copied ONCE
END

db<>fiddle demo