capture data changes on tables

Question

capture data changes on tables

savibp3

Right there with Babe

Points: 752
More actions
March 8, 2017 at 11:47 am

#321070

I need to stored procedure to capture data changes like insert update and delete on the table.Any suggestions please welcome.

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

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

savibp3 - Wednesday, March 8, 2017 11:47 AM
I need to stored procedure to capture data changes like insert update and delete on the table.Any suggestions please welcome.

Lookup Trigger in SQL Server Books Online

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 2

The other option, presuming you have SQL Enterprise or SQL 2016 SP1, is to use change data capture.
https://technet.microsoft.com/en-us/library/bb522489(v=sql.105).aspx

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Lowell SSC Guru Points: 323518 More actions · Answer 3

if you need old vs new and also whodunnit information, a trigger it the way to go.

if you need old vs new, but not whodunnit, Change Data Tracking is great.

if you need whodunnit, but not what exactly changed, you can use SQL Audit. you could also use Extended Events, or an old fashioned SQL trace.

If you just need what changed since the last time you pulled some data, Change Tracking is a great tool.

SQL2016 Standard Edition with SP1 also allows Change Tracking, so you can know which rows changed, but you cannot compare it to old vs new, just know which rows changed via the history of changes limit you put in to track is (ie 3 days, 7 days, etc)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Joe Torre SSChampion Points: 10248 More actions · Answer 4

If you use SQL Server 2016 Temporal tables with the right columns such as ModifiedBy can provide data as it was at any given time and who updated it to that value.

kaj SSCommitted Points: 1607 More actions · Answer 5

Since you mention you need to capture changes made by a stored procedure, I will also mention that you can use the OUTPUT clause to copy before and after values to a table variable. This is primarily useful, if you want to process the captured changes later on in the stored procedure.

https://www.simple-talk.com/sql/learn-sql-server/implementing-the-output-clause-in-sql-server-2008/