Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Delete records within Instead Of Delete trigger

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?

like image 414
Caveatrob Avatar asked Jul 16 '10 15:07

Caveatrob


People also ask

Can we delete record in trigger?

Trigger After Delete Salesforce executes the custom logic after the data is deleted from the Salesforce Database. If you are looking to delete related records, you can make use of Trigger After Delete Salesforce.

How do you delete a record from a trigger in SQL?

First, specify the name of the trigger which you want to create after the CREATE TRIGGER keywords. Second, use BEFORE DELETE clause to specify that the trigger is invoked right before a delete event. Third, specify the name of the table that the trigger is associated with after the ON keyword.

Does truncate invoke delete trigger?

When used with partitioned tables, TRUNCATE TABLE preserves the partitioning; that is, the data and index files are dropped and re-created, while the partition definitions are unaffected. The TRUNCATE TABLE statement does not invoke ON DELETE triggers. Truncating a corrupted InnoDB table is supported.

What is instead of trigger?

An INSTEAD OF trigger is an SQL trigger that is processed “instead of” an SQL UPDATE, DELETE or INSERT statement. Unlike SQL BEFORE and AFTER triggers, an INSTEAD OF trigger can be defined only on a view, not a table.


2 Answers

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.

like image 78
HLGEM Avatar answered Sep 21 '22 02:09

HLGEM


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

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

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
like image 22
etoisarobot Avatar answered Sep 19 '22 02:09

etoisarobot