How to get use text columns in a trigger

Posted by Jeremy on Stack Overflow See other posts from Stack Overflow or by Jeremy
Published on 2010-04-19T17:36:16Z Indexed on 2010/04/19 18:03 UTC
Read the original article Hit count: 404

Filed under:
|
|

I am trying to use an update trigger in sql 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

Of course, I get an error "Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables."

I tried joining to MyTable instead of deleted, but because the insert triger 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?

© Stack Overflow or respective owner

Related posts about triggers

Related posts about sql-triggers