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)

10 comments:

  1. What will happen when you want to take that DELETED records that jsut change and create a history record? I have a trigger that fires when a change is made to that record. I would like to take all that record and create a hostory record. I recieve this Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables. when i to insert into a new table from the deleted.

    Thank you.
    Andrew

    ReplyDelete
  2. Hi!

    Thanks for sharing this kind of information.

    This information really solve my problem.

    Regards,
    Faheem Ahmad

    ReplyDelete
  3. This is a great solution, Ive been looking for this for the whole day and it solve my problem

    thanks a lot

    ReplyDelete
  4. Thanks you Lord for lurking me to this web page, all my hardships and problems was solved by this solution

    ReplyDelete
  5. Thank you the instead of trigger note allowed me to come up with the perfect solution. I wanted to move records to a deleted table when removed from the main table

    ReplyDelete
  6. Thank you so much, this solution help me passed through.

    ReplyDelete
  7. Thank You. This solved my issue.

    ReplyDelete