Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
pmccann1
Posting Yak Master
107 Posts |
 Posted - 2008-03-11 : 14:03:31
|
| is there a way of finding out if anything has been changed in a table |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2008-03-11 : 20:27:18
|
| Changed since when? If you are talking about data, you have to find a way of doing it yourself, such as remembering a point in time or an ID then query the new value & compare it to the new. If you are talking about the definition of the table then yes (I can't remember how) but there is something wrong if you need to know that! |
 |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2008-03-11 : 21:40:50
|
it would largely depend on "what", and what might be available as a checking source (like a backup or replicated data set?)
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
pmccann1
Posting Yak Master
107 Posts |
Posted - 2008-03-12 : 10:03:45
|
| i want to know what data has been changed, is there a way of checking who changed it |
|
|
|
elancaster
A very urgent SQL Yakette
1208 Posts |
Posted - 2008-03-12 : 10:09:09
|
| not retorspectively. going forward you can look at triggers or profiler maybeEm |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-03-12 : 12:50:06
|
quote:
Originally posted by pmccann1
i want to know what data has been changed, is there a way of checking who changed it
For tracking this, you can have audit columns on your table like datemodified,modifiedby,... and you can have a trigger to put the current date and current logged in persons name as values to them. this will enable them to track when & who did the modification last. If you want the entire history of modifications you can have a AUDIT table and populate this by means of the trigger. each time some actions take place. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-03-12 : 23:35:38
|
| Or look at db log with third party tools. |
|
|
|
agelaus
Starting Member
3 Posts |
Posted - 2008-03-13 : 01:09:16
|
| For this we made a Trigger in our class. Ill type out the code we used.USE <Your Database>GO--Create an Auditing TableCREATE TABLE Audit<table Name> --table name is the table you want to -- track changes on(AuditTrailID INT IDENTITY,AuditEvent NVARCHAR(6), --What was doneBeforeAfter NVARCHAR(6), --Befor or after changeAuditUsername NVARCHAR(30), --Who did the changeAuditDate DATETIME,--Next you will list the columns of the table you are auditing<columns of audited table>...);GOCREATE TRIGGER <triggerName>ON <audited Table> AFTER UPDATEAS INSERT INTO AuditAgents SELECT 'Update', 'Before', USER_NAME(), GETDATE(), <columns of the table>... FROM deleted; INSERT INTO AuditAgents SELECT 'Update', 'After', USER_NAME(), GETDATE(), <columns of the table>... FROM inserted;GOYou can then run a query on the Audit table to see what changes have been made. |
|
|
|
|
|
|
|
|
changes to tables