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):
|
Notes:
- 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.
You are such genius
ReplyDeleteWhat 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.
ReplyDeleteThank you.
Andrew
Hi!
ReplyDeleteThanks for sharing this kind of information.
This information really solve my problem.
Regards,
Faheem Ahmad
This is a great solution, Ive been looking for this for the whole day and it solve my problem
ReplyDeletethanks a lot
Thanks you Lord for lurking me to this web page, all my hardships and problems was solved by this solution
ReplyDeleteWorks Great
ReplyDeletety for this post
ReplyDeleteThank 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
ReplyDeleteThank you so much, this solution help me passed through.
ReplyDeleteThank You. This solved my issue.
ReplyDeleteThank you very much, this solved my issue. Thanks!
ReplyDeleteThumbs Up!
ReplyDeleteIronhead's Ironhead's Ironhead Stainless Steel Stainless Steel
ReplyDeleteIronhead's Ironhead Stainless Steel Stainless Steel Stainless Steel Stainless Steel Stainless Steel Stainless sunscreen with zinc oxide and titanium dioxide Steel Stainless Steel dewalt titanium drill bit set Ironhead Stainless Steel Stainless t fal titanium Steel 먹튀 Stainless Steel microtouch titanium Stainless Steel Stainless Steel Rating: 4.4 · 1 review