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

问题描述

我希望有一个而不是删除触发器，以便我可以从要删除的表行中获取文本字段值，以在实际删除时保留这些字段。由于某种原因，我无法在标准的Delete触发器中将它们从Deleted表中拉出（SQL错误退出）。

有没有一种方法可以在

还是在删除行后保存文本字段以保存到新记录中的更好方法？

p>

解决方案

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

 创建表dbo.mytesting（test_id int，文本文本）
 go 
创建表dbo.myaudit（test_id int，文本文本）
 go 
插入dbo.mytesting 
值（1，'test'）
 go 
 
在dbo.mytesting上创建触发器audit_Table_Deletes INSTEAD OF删除
作为
如果@@ rowcount = 0返回; 
插入dbo.myaudit（test_id，sometext）
从删除的d 
中选择d.test_id，t.sometext加入dbo.mytesting t on.test_id = d.test_id 
 
删除dbo.mytesting其中的test_id（从已删除的列表中选择test_id）
进入
 
删除dbo.mytesting，其中test_id = 1 
选择*从dbo.mytesting 
 select * from dbo.myaudit 
转到
 
放置表dbo.mytesting 
放置表dbo.myaudit 
  

如果您可以将字段更改为varchar（max）或nvarchar（max），那是最好的解决方案。不推荐使用text和ntext，在下一版本中应将其从SQL Server中完全删除。

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?

解决方案

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.

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