For years I’ve been using a datetime column as a “row version” or “time stamp” for most of my SQL tables. The column definition in SQL looks like this:
LastChanged datetime NOT NULL DEFAULT (GetDate())
A trigger updates the LastChanged:
IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'dbo.OnExampleChanged')) DROP TRIGGER dbo.OnExampleChanged GO CREATE TRIGGER dbo.OnExampleChanged ON dbo.Example AFTER UPDATE AS SET NOCOUNT ON UPDATE dbo.Example SET LastChanged = DEFAULT WHERE Id IN (SELECT Id FROM inserted) GO
In LINQ-to-SQL, the LastChanged column needs to be set up as:
Auto Generated Value: True Auto-Sync: Always Time Stamp: True Update Check: Never
However, if you try this with Entity Framework, you may experience some random errors when performing an SQL update, where the row fails to update. Entity Framework uses datetime2 by default and this seems to be the cause of the problem, as they have different precisions.
So, what’s the easiest way to update all our LastChanged columns? Start with the following SQL:
SELECT t.name AS TableName ,c.name AS ColumnName ,y.name AS TypeName FROM sys.tables t JOIN sys.columns c ON t.object_id = c.object_id JOIN sys.types y ON c.system_type_id = y.system_type_id WHERE c.name = 'LastChanged' AND y.name = 'datetime' ORDER BY t.name ,c.name
Save the results to a CSV file.
Use the following C# string format:
-- Convert {0}.{1} to datetime2
IF EXISTS (SELECT *
FROM sys.tables t
JOIN sys.columns c ON t.object_id = c.object_id
JOIN sys.types y ON c.system_type_id = y.system_type_id
WHERE t.name = '{0}'
AND c.name = '{1}'
AND y.name = '{2}')
BEGIN
DECLARE @Name varchar(128),
@Cmd varchar(500)
SET @Name = (SELECT name
FROM sysobjects so JOIN sysconstraints sc ON so.id = sc.constid
WHERE OBJECT_NAME(so.parent_obj) = '{0}'
AND so.xtype = 'D'
AND sc.colid = (SELECT colid FROM syscolumns
WHERE id = OBJECT_ID(N'dbo.{0}')
AND name = '{1}'))
SET @Cmd = 'ALTER TABLE {0} DROP CONSTRAINT ' + @Name
EXEC(@Cmd)
ALTER TABLE dbo.{0} ALTER COLUMN {1} datetime2 NOT NULL
ALTER TABLE dbo.{0} ADD DEFAULT (SysDateTime()) FOR {1}
END
GO
Note that we now use SysDateTime() instead of GetDate(). The existing triggers will still work because they call DEFAULT.
Recent Comments