在替代删除触发器中删除记录 [英] Delete records within Instead Of Delete trigger

问题描述

我想要一个而不是删除触发器，以便我可以从正在删除的表行中获取文本字段值，以在实际删除发生时保留这些字段.出于某种原因，我无法从标准删除触发器中的已删除表中提取它们(SQL 错误输出).

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?

推荐答案

这个方法应该可以解决问题.而不是触发器将执行您指定的任何操作，而不是自动执行删除操作.这意味着您手动删除其中的记录是关键，否则记录将不会被删除.它不会递归运行.它只能在没有启用级联删除的表上完成.基本上，诀窍是您加入 id 字段上的原始表，以便从已删除的伪表中您无权访问的字段中获取数据.

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

如果您可以将字段更改为 varchar(max) 或 nvarchar(max)，那是最好的解决方案.Text 和 ntext 已弃用，应在下一版本中从 SQL Server 中完全删除.

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.

这篇关于在替代删除触发器中删除记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！