Identify Insert, update, delete changes and its records in SQL DB

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

Hi,

I want to identify the DML(Insert, update, delete) changes in a database along with the records in a particular table modified in SQL database.

I came accross Change Data Capture but in that way I cannot find the records modified.

Pls help.

Regards,
DhivyaJ

Posted 3-Nov-13 19:45pm

Dhivya.J

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Pratik Bhuva · Answer 1 · 2013-11-03T21:24:00

There is very Good Functionality in sql to do the task.
TRIGGER.

Here is Good Article from codeproject...
Triggers -- Sql Server[^]

And Here is very good video for Triggers with practical examples.
http://www.youtube.com/watch?v=JNb54seLzZY[^]

Hope This Help You.
---------------------
Pratik Bhuva

S. M. Ahasan Habib · Answer 2 · 2013-11-03T21:15:00

Solution 1

If you want row data then you can go for trigger. inside sql server you can find new/deleted/modifed records.

inside trigger you find 2 special tables named INSERTED and DELETED. SQL server automatically create and mange both tables and both have same structure.

From that tables you can collect old/deleted and new row.

http://stackoverflow.com/questions/3336319/using-inserted-and-deleted-tables-in-the-triggers[^]

Posted 3-Nov-13 21:15pm

S. M. Ahasan Habib

Comments

Dhivya.J 4-Nov-13 3:37am

Thank you. Is the trigger you mentioned are table and record specific?

Dhivya.J 4-Nov-13 3:38am

I have nearly 600 tables in one DB and my SQL contains 3 databases like this. Is it possible to use trigger in this case?

S. M. Ahasan Habib 4-Nov-13 4:04am

Yes it is possible but that type of huge triggers management might be difficult and create maintenance overhead. My suggestion is try to do it from your application logic/business logic layer with proper object oriented programming. It will help you to keep simple to your databases.