May 25, 2017 at 1:30 pm
I want to keep a value in my column to remain the same value when a value in another column changes. A computed column call it captureddate) such as:
[captureddate] AS (case [arrive] when 'AB' then CONVERT([varchar],[arrivedate],(111)) end) PERSISTED,
will not keep the captureddate permanent if the row gets updated or changed,
??
zo
May 26, 2017 at 8:35 am
You'll need to provide a little more information or details, some DDL and data.
May 26, 2017 at 8:48 am
I think you probably need to look at Change Data Capture or security auditing, although I think the latter capture DDL, not DML, changes.
John
May 26, 2017 at 9:04 am
Zososql - Thursday, May 25, 2017 1:30 PMI want to keep a value in my column to remain the same value when a value in another column changes.
maybe instead of a computed column, which will always change when its source columns change, you need a regular column and a update trigger that only sets the value of captureddate if it is currently NULL
May 26, 2017 at 4:15 pm
OK, thanks for all insights / advice, if I have more roadblock with this, i'll come back with more indepth questions/sample data.
Zo
May 26, 2017 at 4:37 pm
Unfortunately, there is no elegant way to this. This is actually a recent community ask posted to Microsoft as a new feature.
JediSQL points out the difficulty of capturing the old data plus the SQL that's doing the UPDATE.
https://www.sqlservercentral.com/Forums/1877279/BEFORE-TRIGGER-Idea-Posted-to-Microsoft-Connect
https://connect.microsoft.com/SQLServer/feedback/details/3127222/before-trigger-behavior-with-execute-original-statement
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply