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.
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):
- This trigger works if only one row is affected after an INSERTE/UPDATE/DELETE statement. It will not work correctly if multiple rows happen.
- 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.