SQL Server 2005-将列设置为只读

编写一个存储过程来执行此表中的更新,并确保它不接触InsertTime字段。 然后设置所有用户的权限,这样他们自己就不能对此表进行原始更新,但可以调用存储过程。

您可以通过创建一个更新触发器来实现“只读”字段,该触发器检查该列的更新,然后将其回滚。

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

另一种方法是重新创建insertdate的原始值。 当更新一行时,原始行将移动到(逻辑)“已删除”表中。 所以…

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)

替代触发器也可以完成此任务。

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 

我最近实现了一个非常类似的解决方案,并且必须克服许多障碍,所以我在这里发布这个解决方案,希望它能有所帮助。

我想做的是创建一个记录并最后修改一个列,该列由数据库而不是用户代理管理。在这种情况下,recordcreated将是第一次插入行时的日期时间,lastmodified将是最后一次更新行。我还想防止用户直接修改这些列。我是这样做的:

将这两列作为可为空的smalldatetime列添加到我的表中:

RecordCreated SMALLDATETIME,
LastModified SMALLDATETIME

为表创建了after触发器:

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

触发器首先检查用户是否尝试插入/修改我的列的值。我遇到了update()函数总是返回true的问题,因此需要一些额外的逻辑:

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;

接下来,我有逻辑在每次插入或更新之后向列中添加所需的值。我检查记录是否被插入或更新,并执行适当的逻辑:

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;

此解决方案完全按预期工作。

如本文所述,使用计算列是解决方案之一: Computed Column with Current DateTime?