|
|
Error Message
Server: Msg 272, Level 16, State 1, Line 1
Cannot update a timestamp column.
Causes
The timestamp data type is an 8-byte data type that exposes automatically generated, unique binary numbers within a database. It is generally used as a mechanism for version-stamping table rows.
|
Each database has a counter that is incremented for each insert or update operation that is performed on a table that contains a timestamp column within the database. This counter, the database timestamp, tracks a relative time within a database and not an actual time that can be associated with a clock.
A table can only have one timestamp column. Every time that a row with a timestamp column is modified or inserted, the incremented database timestamp value is inserted in the timestamp column.
Since the timestamp column is automatically generated by the database and is automatically incremented by the database during an update on the table, this error will be encountered if the timestamp column is manually updated through an UPDATE command.
To illustrate, here’s a table that contains a timestamp column:
CREATE TABLE [dbo].[Team] (
[TeamID] INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
[TeamName] VARCHAR(50),
TIMESTAMP )
INSERT INTO [dbo].[Team] ( [TeamName], [Timestamp] )
VALUES ( 'Miami Marlins', DEFAULT )
The following UPDATE statement will generate this error message:
UPDATE [dbo].[Team]
SET [Timestamp] = @@DBTS
WHERE [TeamName] = 'Miami Marlins'
The @@DBTS system function returns the value of the current or last-used timestamp value for the current database.
Solution / Work Around
The timestamp column is usually used to determine whether any value in the row has changed since the last time it was read. If any change is made to the row, the timestamp value is updated. If no change is made to the row, the timestamp value is the same as when it was previously read.
If there’s really a need to update the timestamp column of a table just to mark that particular record as having been updated, here’s one work around that can be done without really affecting the row but having the timestamp column updated:
UPDATE [dbo].[Team]
SET [TeamName] = [TeamName]
WHERE [TeamName] = 'Miami Marlins'
Although the row will contain the same data, the timestamp column will be updated with this UPDATE statement without having this error message generated.
|