SQL Server 2005 - Setting a column as readonly

Question

I have a "InsertTime" field in a table in a SQL Server 2005 database which is defaulted to "getDate()" when the record is first inserted into the database. I want to ensure that this column is not updated again.

Can this column be set to readonly or is there a better way to do this without writing all of the sql for the developers?

Answer 1

You can implement a 'read-only' field by creating an UPDATE trigger that checks for updates to that column and then rolls them back.

IF EXISTS (SELECT name FROM sys.objects
      WHERE name = 'ReadOnlyInsertTime_tr' AND type = 'TR')
   DROP TRIGGER dbo.ReadOnlyInsertTime_tr;
GO

CREATE TRIGGER ReadOnlyInsertTime_tr
ON dbo.MyTable
AFTER UPDATE 
AS 
IF (UPDATE(InsertTime))
BEGIN
ROLLBACK
-- Raise an informative error
-- RAISERROR (50009, 16, 10)
END;
GO

Answer 2

An instead of trigger can also do this job.

CREATE TRIGGER [dbo].[MyTableUpdateTrigger]
ON [dbo].[MyTable]
INSTEAD OF UPDATE
AS
BEGIN
SET NOCOUNT ON

UPDATE MyTable
SET Column1 = Inserted.Column1
,Column2 = Inserted.Column2
-- Don't set this for the "InsertTime" field.
FROM Inserted
INNER JOIN MyTable
ON Inserted.TheKey = MyTable.TheKey
END

Answer 3

Write a stored procedure to do the Update in this table, and make sure it doesn't touch the InsertTime field. Then set all your users' permissions so they can't themselves do a raw Update on this table, but can call the stored procedure.