delete trigger

vikas kanani

my question is when i delete record from table.it will insert on another table...bt point is record included with image datatype.so how it can insert with another table.pls sugest me.

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Anil Kumar14y ago
Why not to use INSTEAD OF trigger? Before delete, copy this record into other table and then delete that record!!!
vikas kanani14y ago
it not worked..error on image datatype...... error is"Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables."
Anil Kumar14y ago
What problem are you facing in this? You can simply use
insert into tblName(fieldsName)
select fieldsName from deleted

Note: MS SQL doesn't allow image field reference in magic tables. So the above will through error if compatibility level is high i.e. 70 otherwise NULL value or Not NULL String value. There are few other data-types that cause the same issue are - ntext, text.

So, above mentioned command wouldn't work and you have to either-
1) convert data-type or
2) you can use Instead Of Trigger in place of After Trigger.