Delete Trigger Usage and Options 1

[Auguy]

I'm confused on which Delete trigger to use (For, After, ...)
I need to save the entire deleted record(s) in another table.
I've found many examples bot not quite sure which is best to use, or what options might be faster
This one I found will work, just wondering if there are other options for the delete trigger are better for what I'm trying to accomplish.

CREATE TRIGGER dbo.SampleTable_DeleteTrigger
ON dbo.SampleTable
FOR DELETE
AS
BEGIN
   INSERT INTO dbo.SampleTable_Audit
   (SampleTableID, SampleTableInt, SampleTableChar, SampleTableVarChar, Operation, TriggerTable)    
   SELECT SampleTableID, SampleTableInt, SampleTableChar, SampleTableVarChar, 'D', 'D'
   FROM deleted;
END;
GO

Auguy
Sylvania/Toledo Ohio

 

[gmmastros]

I do something similar in my database. "For Delete" is probably your best bet in this situation.

With regards to triggers, "For" and "After" are essentially the same thing. There is also an "Instead Of" that is fundamentally different.

In my implementation, I add 2 additional columns to the audit table, one for user and another for DateTimeStamp. So...

INSERT INTO dbo.SampleTable_Audit
   (SampleTableID, SampleTableInt, SampleTableChar, SampleTableVarChar, Operation, TriggerTable[!], UserName, DateTimeStamp[/!])    
   SELECT SampleTableID, SampleTableInt, SampleTableChar, SampleTableVarChar, 'D', 'D'[!], suser_sname(), GetDate()[/!]
   FROM deleted;

This allows you to track who did it, and when it was done.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Chris Miller]

Out of the box, there are three mechanisms SQL Server offers for this type of auditing:

1. Change Tracking
2. Change Data Capture
But the best for your case is
3. Temporal tables:

https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables

Triggers might give you an advantage about the explicit structure of your audit table, but I'd recommend temporal tables as that includes enhanced SQL to make queries involving the main table with current data and the audit (history) data in very convenient ways:

https://docs.microsoft.com/en-us/sq...ing-data-in-a-system-versioned-temporal-table

Chriss

 

[Auguy]

Thanks to both of you.
Just saw these, didn't get an email about them.
I better check my profile about the email setting.
Chris. I will check those out for future use, this project is still sing 2o=104.


Auguy
Sylvania/Toledo Ohio

 

[iambob]

But did it fix your problem though?

Sincerely,
Bob Space

 

[Auguy]

This project has been delayed but I will probably go with George's suggestion.
I really don't need to track all of the changes, just the deletes.


Auguy
Sylvania/Toledo Ohio