SQL Server 2005 - 将列设置为只读

Question

我在SQL Server 2005数据库的一个表中有一个名为“InsertTime”的字段，当记录第一次插入数据库时，默认为“getDate()”。我希望确保此列不会再次更新。

这个列可以被设置为只读吗？还是有更好的方法可以避免开发人员编写所有的SQL语句来实现此功能？

- GordyII

---

我希望避免回滚的需要，只是让它不能被写入。我喜欢你的答案。另一种方法是使用下面的代替触发器，但必须对表进行维护。我不确定哪种方法更好。 - GordyII

Answer 1

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

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

- Mitch Wheat

---

+1 我喜欢这个解决方案；有一件事我想要补充，如果你在 AFTER INSERT 触发器中编写 InsertTime ，那么你需要在该触发器的持续时间内禁用该触发器。 - satnhak

Answer 2

另一种方法是重新创建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)

- Dror

Answer 3

一个代替触发器也可以完成这个工作。

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 

- GordyII

---

如果用户尝试更新被禁止的列，那么如何引发错误？ - Ian

Answer 4

编写一个存储过程来更新这个表，并确保它不会触及InsertTime字段。 然后设置所有用户的权限，使他们不能直接对该表进行原始更新，但可以调用存储过程。

- Vince Bowdren

Answer 5

使用计算列是其中一种解决方案，正如在这篇文章中所解释的那样：如何使用当前日期时间创建计算列？

- zak

Answer 6

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

我想做的是拥有一个由数据库而不是用户代理管理的RecordCreated和LastModified列。在这种情况下，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;

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

- Mark Micallef