created and modified timestamp in sql server 2008
Created timestamp is easy: column of type DATETIME
(for SQL Server up to 2005) or DATETIME2(3)
(2008 or newer) with a DEFAULT CONSTRAINT SYSDATETIME()
SQL Server 2000/2005:
CREATE TABLE dbo.YourTable
( ..... your columns ......,
CreatedTimeStamp DATETIME NOT NULL
CONSTRAINT DF_YourTable_Created DEFAULT (GETDATE())
)
SQL Server 2008 and newer:
CREATE TABLE dbo.YourTable
( ..... your columns ......,
CreatedTimeStamp DATETIME2(3) NOT NULL
CONSTRAINT DF_YourTable_Created DEFAULT (SYSDATETIME())
)
The modified timestamp must be handled with a trigger on each table
CREATE TRIGGER trgYourTableModified
AFTER UPDATE ON dbo.YourTable
AS
UPDATE dbo.YourTable
SET ModifiedDate = GETDATE() -- or use SYSDATETIME() for 2008 and newer
FROM Inserted i
WHERE i.ID = dbo.YourTable.ID
In this trigger, you could also check for your 10 minutes gap - and if the difference is more than 10 minutes, just call ROLLBACK TRANSACTION
in the trigger to stop the update
You need to be aware that triggers in SQL Server are per statement - and not per row! So if your UPDATE
statement updates 50 rows, your trigger will fire once and the Inserted
and Deleted
pseudo-tables inside the trigger will contain 50 rows to deal with.
user2684131
Updated on June 28, 2022Comments
-
user2684131 almost 2 years
I am new to SQL and I want to create timestamp for createdtime and modifiedtime columns. and then compare createdtime with modifiedtime, if modifiedtime is greater than 10 minutes it will not let the row to be updated. How can I achieve this? I am using sql server 2008 R2 Thanks
-
user2684131 almost 11 yearsthanks you already set the time and date as current but I want to use current time means automatically set at the time of storing data at first entry.