COMPUTED COLUMN needing to retain value

1

I need to restore a value in my travellog table by adding a computed column called confirmDate:

TABLE dbo.travellog
(
travelID (pk,varchar(5), NOT NULL),

userID varchar(5) NOT NULL,
travelcode varchar(5) NULL,
departdate datetime NULL,
expectarrrivedate datetime NULL,
CONSTRAINT PK_travelID PRIMARY KEY CLUSTERED (travelID ASC)
);

USE mydb
ALTER TABLE dbo.travellog
ADD [confirmDate] AS (CASE [travelcode] WHEN 'A' THEN cast(GetDate() as Date) END)

Even if the value in confirmDate changes after it gets inserted with the computed column, I need to keep that value in that column. I first used "Persisted" attribute with the column, but i dropped it and re-created it without "Persisted" , and still when the travelcode value changes from 'A' the confirmDate value gets erased.
??

Thanks in advance

2

You want [confirmDate] to be set to "now" when [travelcode] is set to "A", and then to stay with that value if [travelcode] changes again?

That's not what a computed column is for I'm afraid. Computed column just computes based on current data.

I think the only way to achieve this is with a TRIGGER. AFTER UPDATE the Trigger can set [confirmDate] to "now" if the OLD value of [travelcode] was not "A", and the NEW value IS "A".

If [travelcode] changes BACK to "A" a second time do you want [confirmDate] set again? If not then add to the logic so [confirmDate] is only set when "AND [confirmDate] IS NULL"

Note that a trigger must be programmed to operate on a set of data, NOT a single row. So ALL rows in the Insert/Update would be "updated" as a single operation. The trigger has access to pseudo tables DELETED and INSERTED which can be used to compare the Before/After data

3

Thanks, I will be testing this via trigger function.
S