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?
