Recording data changes

Question

Recording data changes

BRP

Default port

Points: 1433
More actions
October 18, 2016 at 6:14 pm

#331097

I'm working with a windows application that uses SQL Server 2012 on windows server 2012 R2. There is something happening randomly in the application that auditing and logging is not picking up or it could be happening at DB level. It makes a change to a particular setting affecting everybody with that functionality.
Is there a trace file or something I can run overnight to pick up any changes to the data? During the day would be horrendous I imagine where overnight processes might be manageable and everything points to this happening outside of work hours.
Any ideas would be greatly appreciated.
Bruce

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 1

When I've run into such things, I create an audit table that's nearly identical to the original table. I say nearly identical because I create the table with no constraints, no referential integrity (a form of constraint), and, if there's an identity column in the original table, I just make the column in the audit table an INT or BIGINT without the IDENTITY property, whichever is appropriate.

I also add several extra columns to the audit table...

1. A column to capture the ORIGINAL_LOGIN().

2. A column to capture the SUSER_SNAME().

3. A column to capture the HOST_NAME().

4. A column to capture the current date and time of the entry into the log table.

5. A column to identify if it was an INSERT, UPDATE, or DELETE.

Then, I write a set-based trigger to capture the data from the DELETED logical table. That will record what the row was changed from. The last active change will always be contained in the original table.

Done correctly, such a trigger can be left to execute even during heavy usage times because, done correctly, it won't impact performance very much at all.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

BRP Default port Points: 1433 More actions · Answer 2

thanks, that looks good. is the delete table specific to each table. I'm thinking I might get away with putting the trigger directly on the apps table which shouldn't get a lot of use.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 3

Bruce-12445 (10/18/2016)
thanks, that looks good. is the delete table specific to each table. I'm thinking I might get away with putting the trigger directly on the apps table which shouldn't get a lot of use.

The DELETED table is a logical table within the trigger for the table the trigger is on. For INSERTs, it will have nothing in it. For UPDATEs and DELETEs, it will contain one row for each row contained in the UPDATE or DELETE and contains what the row looked like before the UPDATE or DELETE.

Each table that you put a trigger on will contain a DELETED logical table that looks like the table the trigger is on. It's auto-magic, as well. You don't have to declare it or configure it (and couldn't if you wanted to).

Your target table (the audit table) should like identical to the original table with the exceptions that I noted. That, you will have to define with a CREATE TABLE, which can be easily generated from the original table and then modified as I suggested.

Have you ever written a trigger before?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

BRP Default port Points: 1433 More actions · Answer 4

I have written triggers a long time ago so I'll give it a go and it all should come rushing back in.

Correct me if I'm wrong: I'll create the audit table and write the trigger on the table that's being changed. the trigger will pick up the extra fields info and what the existing value is in the delete statement and add it to the new audit table. This live table (now I've found it) shouldn't be accessed to much so I can leave the trigger on and hopefully it won't affect the app running the DB.

How am I doing?

Thanks,

Bruce

Eirikur Eiriksson SSC Guru Points: 182881 More actions · Answer 5

Bruce-12445 (10/18/2016)
I have written triggers a long time ago so I'll give it a go and it all should come rushing back in.
Correct me if I'm wrong: I'll create the audit table and write the trigger on the table that's being changed. the trigger will pick up the extra fields info and what the existing value is in the delete statement and add it to the new audit table. This live table (now I've found it) shouldn't be accessed to much so I can leave the trigger on and hopefully it won't affect the app running the DB.
How am I doing?
Thanks,
Bruce

There is an example of simple auditing in this post, you should be able to adjust it to your needs.

😎

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 6

Bruce-12445 (10/18/2016)
I have written triggers a long time ago so I'll give it a go and it all should come rushing back in.
Correct me if I'm wrong: I'll create the audit table and write the trigger on the table that's being changed. the trigger will pick up the extra fields info and what the existing value is in the delete statement and add it to the new audit table. This live table (now I've found it) shouldn't be accessed to much so I can leave the trigger on and hopefully it won't affect the app running the DB.
How am I doing?
Thanks,
Bruce

Sounds right, Bruce. If you have any trouble at all, post back. Happy to help here. And, the trigger should be awfully simple, in this case, because it's a simple full row audit. Nothing fancy going on here. You might even use defaults on the audit table for some of the extra columns I identified like ORIGINAL_LOGIN() and date/time the row was entered into the audit table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

BRP Default port Points: 1433 More actions · Answer 7

Great thanks for that. the other post is a bit complicated, I have a very simple model working and I can only improve on it if necessary. I much appreciate your help.

BRP Default port Points: 1433 More actions · Answer 8

Hi Jeff

I'm trying to add a bit more info into the audit table by adding this:

SELECT client_net_address,local_net_address,host_name,original_login_name,login_name,program_name

FROM sys.dm_exec_connections SCON

OUTER APPLY sys.dm_exec_sessions SES

WHERE SCON.session_id = @@SPID

AND SES.session_id = @@SPID

the problem is permissions when run from the app. is there a simple way around this. if not I'll just include the date.

Bruce

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 9

Bruce-12445 (10/19/2016)
Hi Jeff
I'm trying to add a bit more info into the audit table by adding this:
SELECT client_net_address,local_net_address,host_name,original_login_name,login_name,program_name
FROM sys.dm_exec_connections SCON
OUTER APPLY sys.dm_exec_sessions SES
WHERE SCON.session_id = @@SPID
AND SES.session_id = @@SPID
the problem is permissions when run from the app. is there a simple way around this. if not I'll just include the date.
Bruce

Do you have an error message that is being produced by the app?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

BRP Default port Points: 1433 More actions · Answer 10

Yes it's a couple of pages long so I've got around it by keeping it simple and only getting the fields you suggested.

Thanks,

Bruce

Sergiy SSC Guru Points: 110206 More actions · Answer 11

Bruce-12445 (10/19/2016)
Hi Jeff
I'm trying to add a bit more info into the audit table by adding this:
SELECT client_net_address,local_net_address,host_name,original_login_name,login_name,program_name
FROM sys.dm_exec_connections SCON
OUTER APPLY sys.dm_exec_sessions SES
WHERE SCON.session_id = @@SPID
AND SES.session_id = @@SPID
the problem is permissions when run from the app. is there a simple way around this. if not I'll just include the date.
Bruce

Use system functions instead of querying dm views.

_____________
Code for TallyGenerator

BRP Default port Points: 1433 More actions · Answer 12

BRP

Default port

Points: 1433

October 20, 2016 at 6:22 pm

#1907833

will do, thanks again.