CODEWITHSUNDEEP
databaset-sqlsql-server-2005stored-procedurestriggers
Hell Boy
Hell Boy

Reputation: 899

SQL Procedure to return what column in what table has changed

I want to log any change made to certain tables. i.e if a new record is created updated or deleted I want this to be logged in a separate table. To make it cleaner and nicer I do not what to right logic in Java code but would rather prefer some sort of stored procedure that takes a value in i.e. current logged in user who has made a change, and grabs what kind of action has been performed on what table and on what column ?

Is that even possible ? I have googled it but could not get anything ? any ideas please ?

Upvotes: 0

Views: 74

Answers (3)

arun.passioniway
arun.passioniway

Reputation: 133

In Addition to Andrey's answer, you would need to use INSERTED and DELETED tables to find out what are the past and new values being used/updated and log them accordingly.

Upvotes: 1

mellodev
mellodev

Reputation: 1607

The phrase you should be searching for is "Auditing", and there are quite a few stored procedures that do what you want. They are powered by triggers and write rows to another table based on the actions. You'll probably have to modify them to log which user did the action.

Try something like this: http://www.geekzilla.co.uk/ViewECBC0CC3-1C7E-4E7E-B243-F2F259A5C920.htm

Upvotes: 0

Andrey Gurinov
Andrey Gurinov

Reputation: 2895

You can do it by implementing triggers on your tables. Please refer to this CREATE TRIGGER and UPDATE().

And here is the theory: DML Triggers

Upvotes: 1

Related Questions