How do I list down the names of tables in my database that has changes (new record inserted, record updated or deleted)

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

, +

i have an MS SQL Server Express edition, and i want to know if there were new products encoded in my application thru my database, or if user encoded new employees or change employee information. i want those changes stored in another table so i can easily see what are those and from which tables.

What I have tried:

i was thinking of creating a trigger for this but that would require me to add triggers to each tables. if there's a trigger for the changes in database?

Posted 7-Aug-16 20:24pm

ProgrammingEnthusiast

Updated 8-Aug-16 19:49pm

Add a Solution

Comments

Orcun Iyigun 8-Aug-16 2:46am

You might consider looking up 3 SQL Server auditing features (Change Tracking, Change Data Capture and SQL Server Auditing)

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)

vani suresh · Answer 1 · 2016-08-07T21:32:00

Solution 1

Hi JOVY,

Please follow this lin explained about trigger for insert,update,delete in tables and you can find the sample table and script also.
Trigger-Insert,Update,Delete in tables

Thanks
:)

Posted 7-Aug-16 21:32pm

vani suresh

Daniel Jones · Answer 2 · 2016-08-08T19:49:00

SQL

SELECT 
      [db_name] = d.name
    , [table_name] = SCHEMA_NAME(o.[schema_id]) + '.' + o.name
    , s.last_user_update
FROM sys.dm_db_index_usage_stats s
JOIN sys.databases d ON s.database_id = d.database_id
JOIN sys.objects o ON s.[object_id] = o.[object_id]
WHERE o.[type] = 'U'
    AND s.last_user_update IS NOT NULL
    AND s.last_user_update BETWEEN DATEADD(wk, -1, GETDATE()) AND GETDATE()