How to use INSERTED/DELETED with text/nText/image in SQL Server

I've been looking all over the web to try figuring out this myself today and I think this post gives a very reasonable solution (if not the best) on how to reference text/nText/image fields in the INSERTED/DELETED tables.

Triggers use two logical tables called the INSERTED and the DELETED table. These tables are accessed only inside triggers and you cannot directly modify the data in the tables. The tables hold the new and old rows of the data being inserted or deleted. That is if you:

• DELETE a row , a row will be created in the DELETED table,
• INSERT a row, a row is created in the INSERTED table, and
• UPDATE a row, the INSERTED table will contain the updated row and a previous state of the row will be added to the DELETED table.

This was my original code:

CREATE TRIGGER tr_Source_UPDATE ON tbl_Source AFTER UPDATE AS BEGIN -- Field text in tbl_Source IF UPDATE(text) BEGIN UPDATE t SET t.newtext = i.text FROM tbl_Dest t, INSERTED i, DELETED d WHERE t.PK_ID = i.PK_ID END END

But when I try to execute it, I receved the following error: 'Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.' After some research I found out that:

In a DELETE, INSERT, or UPDATE trigger, SQL Server does not allow text, ntext, or image column references in the inserted and deleted tables if the compatibility level is set to 70. The text, ntext, and image values in the inserted and deleted tables cannot be accessed. To retrieve the new value in either an INSERT or UPDATE trigger, join the inserted table with the original update table. 

Using this last piece of information, I decided to get the text field from the table itself, joining it to the INSERTED table on the primary key. (Remember that when a trigger fires, the INSERT/UPDATE/DELETE has already occurred):

CREATE TRIGGER tr_Source_UPDATE ON tbl_Source AFTER UPDATE AS BEGIN -- Field text in tbl_Source IF UPDATE(text) BEGIN UPDATE t SET t.newtext = s.text FROM tbl_Dest t, tbl_Source s JOIN INSERTED i ON s.PK_ID = i.PK_ID, DELETED d WHERE t.PK_ID = s.PK_ID END END

This code works great for me!!

Notes:

1. This trigger works if only one row is affected after an INSERTE/UPDATE/DELETE statement. It will not work correctly if multiple rows happen. 
2. Another option is to use the INSTEAD OF triggers. In short, an INSTEAD OF trigger fires in place of the actual event, be it INSERT, UPDATE, or DELETE.

Sources: CREATE TRIGGER (Transact-SQL)