SQL 在替代删除触发器中删除记录

Delete records within Instead Of Delete trigger

提问by Caveatrob

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).

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

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?

或者在删除一行后获取文本字段以保存到新记录中的更好方法？

回答by HLGEM

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.

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

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.

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

回答by Caveatrob

None of the answers above worked for me, I am assuming people were using SQL 2008 so this answer works for SQL 2012

上面的答案都不适合我，我假设人们使用的是 SQL 2008 所以这个答案适用于 SQL 2012

CREATE TRIGGER People_LogTriggerDelete ON People
INSTEAD OF DELETE
AS
SET NOCOUNT ON;
INSERT INTO People_AuditLog (Event, Date, Id, Name, LastName, SqlUser)
       SELECT 'DELETE', GETDATE(), d.Id, d.Name, d.LastName, CONCAT(SUSER_SNAME(),'/',@@SERVERNAME)
       FROM People t INNER JOIN deleted d ON t.Id = d.Id

DELETE People FROM People JOIN deleted ON People.Id = deleted.Id

回答by etoisarobot

It is probably better to do this with an After Trigger. Less complex.

使用 After Trigger 执行此操作可能会更好。不太复杂。

If you have the following tables

如果你有以下表格

CREATE TABLE [dbo].[Data](
[DocumentID] [smallint] NULL,
[Data] [nvarchar](max) NULL
) 

and

和

CREATE TABLE [dbo].[Audit](
[DocumentID] [smallint] NULL,
[Data] [nvarchar](max) NULL
) 

The following After trigger should insert a row into the audit table whenever a row is deleted from the Data Table

每当从数据表中删除一行时，以下 After 触发器应向审计表中插入一行

Create Trigger audit_Table_Deletes on dbo.Data for delete 
as
if @@rowcount = 0 return;
Insert into audit (DocumentID, Data) Select DocumentID, Data from deleted;
Go

回答by brismith

I once needed to catch records which were being deleted; I wrote a trigger which would copy records into a different table with the same structure as the first one: it went something like this:

我曾经需要捕捉被删除的记录；我写了一个触发器，它将记录复制到与第一个具有相同结构的不同表中：它是这样的：

create trigger [delta_del] on [customer]
for delete
as

declare <variables> from deleted;
open mycurs
fetch next from mycurs into @v1, @v2
while (@@fetch_status = 0
begin
insert into delta (fields) values (@v1, @v2)
fetch next from mycursor into @v1, @v2
end

close mycursor
deallocate mycursor

etc.