created and modified timestamp in sql server 2008

Question

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

Answer 1

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.