Cannot use text, ntext, or image columns in the ‘inserted’ and ‘deleted’ tables — Use "INSTEAD OF" triggers in SQL 2005 / SQL 2008

Problem: Cannot use text, ntext, or image columns in the ‘inserted’ and ‘deleted’ tables.

Solution: Use "INSTEAD OF" triggers

Description: INSTEAD OF triggers basically replace the action of a normal trigger.  The DELETE trigger will delete the row automatically, with INSTEAD OF DELETE you have to manually write the DELETE part yourself or the row won’t get deleted.  Here, you have access to the text/ntext/image fields available in the ‘deleted’ / ‘inserted’ virtual tables.

Code:

CREATE TRIGGER [dbo].[trg_test_table_delete]
ON [dbo].[test_table]
INSTEAD OF DELETE
AS
BEGIN

INSERT INTO test_table_audit
(test_name_old)
SELECT old.test_name
FROM deleted old

DELETE FROM test_table
WHERE testid IN (SELECT testid
FROM deleted
WHERE some-criteria-is-met)

END

Props to the folks on this thread: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/c601ce00-0e2c-4bfd-8b8e-ca444ae219c2

3 thoughts on “Cannot use text, ntext, or image columns in the ‘inserted’ and ‘deleted’ tables — Use "INSTEAD OF" triggers in SQL 2005 / SQL 2008

Leave a Reply

Your email address will not be published. Required fields are marked *