Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Delete Trigger Usage and Options 1

Status
Not open for further replies.

Auguy

Programmer
May 1, 2004
1,206
US
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.
Code:
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
 
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...

Code:
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
 
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:
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:


Chriss
 
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
 
But did it fix your problem though?

Sincerely,
Bob Space
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top