May 3, 2017 at 10:38 pm
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
May 3, 2017 at 11:24 pm
Create a trigger that updates the value of confirmDate
May 4, 2017 at 2:34 am
The value of a persisted column is calculated at select time, not at insert or update time - consider the code below and you'll see that. That's why the persisted column expression must be deterministic, so I'm not sure how you managed to persist the column. Use a trigger instead, as Des suggested.CREATE TABLE #travellog (
travelID int NOT NULL IDENTITY PRIMARY KEY CLUSTERED
, travelcode varchar(5)
, confirmDate AS CASE travelcode WHEN 'A' THEN GETDATE() END
)
INSERT INTO #travellog
VALUES ('A'), ('B')
SELECT * FROM #travellog
WAITFOR DELAY '00:00:10'
SELECT * FROM #travellog
John
May 4, 2017 at 9:14 am
Zososql - Wednesday, May 3, 2017 10:37 PMI 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
Assuming it is possible, the best way would be to modify the code that inserts/updates this table to simply put in the correct value. You would need some logic for this, and potentially some data too.
Otherwise I don't know why you need a computed column. A trigger could set it/reset it as appropriate. Look up triggers in Books Online.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 4, 2017 at 12:08 pm
John Mitchell-245523 - Thursday, May 4, 2017 2:34 AMThe value of a persisted column is calculated at select time, not at insert or update time - consider the code below and you'll see that.
If I'm reading that correctly, that's not quite right. The definition you have above is for non-persisted computed columns. Persisted computed columns are materialized and physically stored in the table. Think of them as a "micro indexed view for a single column".
Please see the following MS documentation, particularly the section titled "Persisted".
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-table-computed-column-definition-transact-sql
--Jeff Moden
Change is inevitable... Change for the better is not.
May 4, 2017 at 12:19 pm
Zososql - Wednesday, May 3, 2017 10:37 PMEven 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
How did you ever do that? GETDATE() is non-deterministic, so it can't be used for a PERSISTED computed column.
In this case, you might want to include a trigger for insert and update to get the moment when the value is defined as 'A'.
May 5, 2017 at 2:01 am
Jeff Moden - Thursday, May 4, 2017 12:08 PMJohn Mitchell-245523 - Thursday, May 4, 2017 2:34 AMThe value of a persisted column is calculated at select time, not at insert or update time - consider the code below and you'll see that.If I'm reading that correctly, that's not quite right. The definition you have above is for non-persisted computed columns. Persisted computed columns are materialized and physically stored in the table. Think of them as a "micro indexed view for a single column".
Please see the following MS documentation, particularly the section titled "Persisted".
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-table-computed-column-definition-transact-sql
Sorry, slip of the pen. I did indeed mean a computed column, and a non-persisted one at that.
John
May 5, 2017 at 5:25 am
John Mitchell-245523 - Friday, May 5, 2017 2:01 AMJeff Moden - Thursday, May 4, 2017 12:08 PMJohn Mitchell-245523 - Thursday, May 4, 2017 2:34 AMThe value of a persisted column is calculated at select time, not at insert or update time - consider the code below and you'll see that.If I'm reading that correctly, that's not quite right. The definition you have above is for non-persisted computed columns. Persisted computed columns are materialized and physically stored in the table. Think of them as a "micro indexed view for a single column".
Please see the following MS documentation, particularly the section titled "Persisted".
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-table-computed-column-definition-transact-sqlSorry, slip of the pen. I did indeed mean a computed column, and a non-persisted one at that.
John
NP. You should see what I sometimes come up with before coffee. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
May 8, 2017 at 9:57 pm
Is "Approval" the only thing which happens to the travels?
Do they get cancelled, rescheduled, etc.?
Such events related to travels must be recorded in a separate TravelEventLog table with a EventDate assigned for each record.
By using a correct data model you avoid tricky problems.
_____________
Code for TallyGenerator
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply