How to save my deleted data?
-
I have a store proc in which it deletes and updates nearly 100 tables. now I want to keep track of all deleted and updated records, how can i do that. what is the best and simple way to do.
-
The traditional way is to create a set of identical audit tables to those you are updating/deleting and create audit triggers... something like:
CREATE TRIGGER aud_myTable_upddel
ON myTable
AFTER UPDATE, DELETE
AS
IF @@ROWCOUNT = 0 RETURN;
INSERTmyTable_audit
SELECT *, GETDATE(), SUSER_SNAME() FROM DELETED
-
so do i need to create nearly 100 identical tables to audit them.
-
Yep! There are a couple of alternatives. One is to have audit tables that are identical to the table being audited, along with additional fields to track who and when did the change. Another is to use XML to capture the entire record, which can let you audit your 100 tables with only one audit table, but this makes change roll-backs difficult. And you might want to track the workstation and login name in case someone is trying to get tricky and might have compromised an account.
(you could also store the audits in a seperate DB to make them more secure in case the DB is compromised)
There are a lot of very good articles on this site that address this, just search for Audit.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font] -
so when am trying to keep track of changes on the tables, I want to know
deleted row, when updated, who updated. from these 3 I can find the 1st 2 but how am going to find the person name who deleted/updated that?
-
Try these:
print suser_sname()
print current_user
And remember, BOL is your friend.
(actually, Aaron's previous post used the first)
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font] -
As for the question "so do i need to create nearly 100 identical tables to audit them", do you just want to keep track or actually keep the data?
-
when i use suser_sname() am getting the application user name which is a role for application, i need the end users name who performs the delete/update?
-
There are lots of system calls that provide user info, unfortunately I don't know off-hand which one would return what you need. You're going to have to hit BOL and look at code on this site to find it.
It's possible that if you're running a multi-tier system that the info is not available, i.e. not being passed down from the server above the database. I have such an app, and I get one login on who knows how many threads. The only time that I get identifying info is when someone is running/developing a Crystal Report.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply