如何在触发器中使用文本列 [英] How to get use text columns in a trigger

问题描述

我正在尝试在SQL Server 2000中使用更新触发器，以便在发生更新时，在历史记录表中插入一行，以便将所有历史记录保留在表中：

I am trying to use an update trigger in SQL Server 2000 so that when an update occurs, I insert a row into a history table, so I retain all history on a table:

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列。

Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.

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

I tried joining to MyTable instead of deleted, but because the insert trigger fires after the insert, it ends up inserting the new record into the history table, when I want the original record.

我该怎么做，仍然使用文本列？

How can I do this and still use text columns?

推荐答案

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

the only way to do this is keep the current row in the history table too. Do what you tried and join to the actual table, and insert all columns from current actual table into the history table.

您可能会遇到类似这样的情况，其中仅历史记录具有上一个版本的行：

you probably have something like this, where the history only has the previous version of a row:

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

我是说这样做，每个版本都存储在其中：

I'm saying do something like this, where every version is stored:

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

这样，只要有插入或更新，只要将当前行插入历史记录表，就可以复制当前行，但这并不可怕。

this way whenever there is an insert or update, just insert the current row into the history table, you have a duplicate of the current row, but that isn't that terrible.

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

If you are just adding this trigger to an existing table with data in it, then run a query like this to prepopulate all the current values for you:

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

这篇关于如何在触发器中使用文本列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！