Delete records within Instead Of Delete trigger

Question

I want to have an instead of delete trigger so that I can get text field values out of the table row that's being deleted to preserve those fields when the actual delete occurs. For some reason I can't pull them from the Deleted table in a standard Delete trigger (SQL errors out).

Is there a way to do an actual delete within in an "instead of delete" trigger without making the trigger refire?

Or a better way of getting text fields once a row is deleted to save into a new record?

Answer 1

This method should do the trick. An instead of trigger will do whatever you specify instead of doing the delete automatically. This means it is key that you do the delete in it manually or the record will not get deleted. It will not run recursively. It can only be done on a table without cascade delete enabled. Basically the trick is you join to the orginal table on the id field in order to get the data from the field you don't have access to in the deleted pseudotable.

create table dbo.mytesting (test_id int, sometext text)
go
create table dbo.myaudit (test_id int, sometext text)
go
insert into dbo.mytesting
values (1, 'test')
go

Create Trigger audit_Table_Deletes on dbo.mytesting INSTEAD OF delete  
as 
if @@rowcount = 0 return; 
Insert into dbo.myaudit (test_id, sometext) 
Select d.test_id, t.sometext from deleted d 
join dbo.mytesting t on t.test_id = d.test_id

Delete dbo.mytesting where test_id in (select test_id from deleted)
go

delete dbo.mytesting where test_id = 1
select * from dbo.mytesting
select * from dbo.myaudit 
Go 

drop table dbo.mytesting
drop table dbo.myaudit

If you can change the field to varchar(max) or nvarchar(max) that is the best solution though. Text and ntext are deprecated and should be removed altogether from SQL Server in the next version.