How to know when data inside a table has been modified?

Question

How to know when data inside a table has been modified?

SQL ORACLE

One Orange Chip

Points: 27807
More actions
December 3, 2010 at 10:07 am

#229361

Not table structure, but data.
Any input will be greatly appreciated.

Viewing 7 posts - 1 through 6 (of 6 total)

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

......................... SSC Eights! Points: 983 More actions · Answer 1

There are some scripts floating around that do this to some degree, but I like Data Compare from Red Gate or Change manager from Embarcadero.

Both can automatically monitor data and report on or notify when data changes.

mtassin SSC-Insane Points: 23099 More actions · Answer 2

Triggers can do this... could even call sp_send_dbmail to alert you to the change if you wanted it to.

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 3

SQL ORACLE (12/3/2010)
Not table structure, but data.
Any input will be greatly appreciated.

Hi

If your table is having datetime field then you can use order by datetime desc

or if you dont have the datetime field then add new column

ALTER TABLE MYTABLE ADD date DATETIME

go

ALTER TABLE dbo.MyTABLE ADD CONSTRAINT

DF_MyTABLE_date DEFAULT GETDATE() FOR date

Which will help future rows, but is not going to tell when rows that are already in the table were created.

or Try with trigger

Even thgouh you need when the data has been added then try below things

Use 3rd party Tool log Rescue (if you u have from beginning) and find date and time row by row record inserted in your table you will be getting. If You need older datas date and time(when it has been modified) then do it one by one as it in Log file.

If you or not having Log then just leave it no other way is there to get when data inside a table.

Thanks

Parthi

Thanks
Parthi

Adiga One Orange Chip Points: 27284 More actions · Answer 4

You can also make use of Change Data Capture in SQL Server 2008.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

GSquared SSC Guru Points: 260824 More actions · Answer 5

There's a whole article on it here:

http://www.sqlservercentral.com/articles/Auditing/63247/

http://www.sqlservercentral.com/articles/Auditing/63248/

I think the articles are awesome. But I wrote them, so I may not be completely objective about that. 🙂

If you read the articles, make sure to also read the discussions. Lots of good data there that adds a lot to the subject.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

@SQLFRNDZ SSCrazy Eights Points: 9095 More actions · Answer 6

I would prefer Change data capture very easy to use ad more information you get

This is all if you are in sql server 2008.

[font="Tahoma"]
--SQLFRNDZ[/url]
[/font]