Insert changed data

Question

Insert changed data

tt-615680

SSCertifiable

Points: 7835
More actions
August 24, 2016 at 11:30 am

#312773

Dear all,
I'm trying to find out the best why of inserting into a column e.g "ChangedData" if it has either been 'updated', 'deleted' or inserted.
Thank you in advance!

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

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

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

tt-615680 (8/24/2016)
Dear all,
I'm trying to find out the best why of inserting into a column e.g "ChangedData" if it has either been 'updated', 'deleted' or inserted.
Thank you in advance!

perhaps you can provide some example data that describes what you mean.

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

Let's see if I understand correctly.

You want to change the value of a column to track changes on the table? Are your queries prepared to handle soft deletes? If you don't plan on handling soft deletes, you need to find a different way to track those changes.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

tt-615680 SSCertifiable Points: 7835 More actions · Answer 3

I have 2 tables which has the following columns

Table1

ID,

CustomerName,

CustomerID

...,

DateofUpdatedData

Table2

ID,

CustomerName,

CustomerID

...,

DateofUpdatedData

DataupdateType

I want to insert into the "DataupdateType" column of table2 any data which has been either 'Updated', 'Deleted' or 'Inserted'

I hope that is clear but please let me know if you need me to explain further.

Thank you!

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 4

You need to write a trigger to do this.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 5

p.s. It's also a huge waste of disk space to audit INSERTs. Only audit UPDATES and DELETEs. INSERTs will always be in the original table until they're updated, which is when the original data (the INSERT) would be copied to the audit table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)