Get datetime based on change in another column using UDF and computed column in TSQL

Given: Given a Microsoft SQL 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.