如何在触发器中使用文本列

Question

我试图在 SQL Server 2000 中使用更新触发器，以便在发生更新时，我将一行插入到历史表中，这样我就保留表上的所有历史记录：

CREATE Trigger trUpdate_MyTable ON MyTable
FOR UPDATE
AS
    INSERT INTO
        [MyTableHistory]
        (
           [AuditType]
           ,[MyTable_ID]
           ,[Inserted]
           ,[LastUpdated]
           ,[LastUpdatedBy]
           ,[Vendor_ID]
           ,[FromLocation]
           ,[FromUnit]
           ,[FromAddress]
           ,[FromCity]
           ,[FromProvince]
           ,[FromContactNumber]
           ,[Comment])
    SELECT
        [AuditType] = 'U',
        D.*
    FROM
        deleted D
    JOIN    
        inserted I ON I.[ID] = D.[ID]

GO

当然，我收到错误

不能在“插入”和“删除”表中使用 text、ntext 或 image 列。

我尝试加入 MyTable 而不是删除，但因为插入触发器在插入后触发，所以当我想要原始记录时，它最终会将新记录插入到历史表中。

我怎样才能做到这一点并仍然使用文本列？

Answer 1

根据SQL Server 2000 CREATE TRIGGER

在删除、插入或更新中
触发器，SQL Server不允许
text、ntext 或 image 列
插入和删除的引用
表如果兼容性级别是
等于 70。text、ntext 和
插入的图像值和
无法访问已删除的表。到
检索新值
INSERT 或 UPDATE 触发器，加入
插入原始表
更新表。 ...

如果兼容性级别为 80 或
更高，SQL Server允许更新
text、ntext 或 image 列
通过 INSTEAD OF 触发器
表或视图。

那么，如果我正确地阅读了本文（自从我使用 '2000 以来已经有一段时间了），

• 您可以使用 INSTEAD OF 触发器吗？
• 兼容级别是多少？

Answer 2

执行此操作的唯一方法是将当前行也保留在历史记录表中。执行您尝试的操作并加入实际表，并将当前实际表中的所有列插入历史表中。

你可能有这样的事情，其中​​历史记录只有一行的先前版本：

YourTable
ID  value1   value2
1   AAA      AAAA
2   BBB      BBBB
3   CCC      CCC3

YourTableHostory
HistoryID HistoryDate ID value1  value2
1         4/17/2010   2  CCC     CCCC
2         4/18/2010   2  CCC     CCC1

我是说做这样的事情，其中​​存储每个版本：

YourTable
ID  value1   value2
1   AAA      AAAA
2   BBB      BBBB
3   CCC      CCC3

YourTableHostory
HistoryID HistoryDate ID  value1  value2
1         4/10/2010   1   AAA      AAAA
2         4/10/2010   2   BBB      BBBB
3         4/10/2010   3   CCC      CCCC
4         4/17/2010   2   CCC      CCC1
5         4/18/2010   2   CCC      CCC2
5         4/19/2010   2   CCC      CCC3

这样每当有插入或更新时，只需将当前行插入到在历史表中，您有当前行的重复项，但这并没有那么糟糕。

如果您只是将此触发器添加到其中包含数据的现有表中，则运行如下查询来为您预填充所有当前值：

INSERT INTO YourTableHostory
        (HistoryDate,ID,value1,value2)
    SELECT
        GETDATE(),ID,value1,value2
        FROM YourTable