Lazy Programmer Notes

One man's crappy software is another man's full time job. ~Jessica Gaston

Saturday, August 29, 2009

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)