SQL Server 2005 - Setting a column as readonly

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 be开发者_开发百科tter way to do this without writing all of the sql for the developers?

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

Another approach is to recreate the original value of InsertDate. When a row is updated the original row is moved to the (logical) "deleted" table. So ...

ALTER TRIGGER trgDocuments
ON dbo.Documents
FOR INSERT, UPDATE
AS

-- For new records set the created date and the updated date to Now
UPDATE Documents SET DateCreated = GetDate(), DateUpdated = GetDate() Where DocumentId in (SELECT DocumentId From inserted)

-- For updated records set the created date to the original created date and the updated date to Now
UPDATE Documents SET DateCreated = (SELECT DateCreated FROM deleted d WHERE DocumentId = d.DocumentId) Where DocumentId in (SELECT DocumentId From deleted)
UPDATE Documents SET DateUpdated = GetDate() Where DocumentId in (SELECT DocumentId From deleted)

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.

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 

I recently implemented a very similar solution to this and had to work through a number of hurdles, so I'm posting the solution here hoping it will help.

What I wanted to do was have a RecordCreated and LastModified column which were managed by the database rather than the user agent. In this case, RecordCreated would be the datetime when the row was first inserted, and LastModified would be the last time the row was updated. I also wanted to prevent the user from modifying these columns directly. Here's what I did:

Added the two columns to my table as nullable SMALLDATETIME columns:

RecordCreated SMALLDATETIME,
LastModified SMALLDATETIME

Created a AFTER trigger for the table:

CREATE TRIGGER dbo.WidgetProductionTrigger ON dbo.WidgetProduction AFTER INSERT, UPDATE AS BEGIN

The trigger first checks if the user has attempted to insert/modify the values of my columns. I encountered a problem with the UPDATE() function always returning true, so a little extra logic was required:

DECLARE @RecordCreated SMALLDATETIME;
SELECT @RecordCreated = RecordCreated FROM INSERTED;
IF UPDATE(RecordCreated) AND NOT @RecordCreated IS NULL BEGIN
    RAISERROR('RecordCreated is not user maintainable.', 16, 1);
    ROLLBACK TRANSACTION;
    RETURN;
END;
DECLARE @LastModified SMALLDATETIME;
SELECT @LastModified = LastModified FROM INSERTED;
IF UPDATE(LastModified) AND NOT @LastModified IS NULL BEGIN
    RAISERROR('LastModified is not user maintainable.', 16, 1);
    ROLLBACK TRANSACTION;
    RETURN;
END;

Next, I have logic to add the required values to the columns after each insert or update. I check to see if the record is being inserted or updated and execute the appropriate logic:

IF EXISTS ( SELECT * FROM DELETED ) BEGIN
    UPDATE dbo.WidgetProduction SET
        LastModified = CAST(GETDATE() AS SMALLDATETIME)
    WHERE ProductionRecordID IN (SELECT ProductionRecordID FROM INSERTED);
END ELSE BEGIN
    UPDATE dbo.WidgetProduction SET
        RecordCreated = CAST(GETDATE() AS SMALLDATETIME),
        LastModified = CAST(GETDATE() AS SMALLDATETIME)
    WHERE ProductionRecordID IN (SELECT ProductionRecordID FROM INSERTED);
END;

This solution works exactly as expected.

Using computed column is one of the solutions, as explained in this post: Computed Column with Current DateTime?