Creating a trace to log values updates by job

Question

Post reply

Creating a trace to log values updates by job

salkobi

Ten Centuries

Points: 1090
More actions
January 16, 2015 at 9:54 am

#294386

Hey All,
This is my first attempt at creating a trace, and i'm having trouble finding documented steps online to point me in the right direction.
I have an agent job in place that updates a value by comparing it to what's listed on a different table. I'm looking to deploy a trace to confirm that the correct data is being modified after the job runs.
The tables included in the job are an orderheader table, a company table and a invoiceheader table. Each table has 2 PKs in common being the Order# and the Customer Code. The value being modified is the Currency Type( USD$ or CAD$) which is a varchar datatype. The job runs 2 separate updates; 1 compared to the company table and 1 compared to the invoiceheader table. Whenever the currency value is Null or <> to the value in the compared table, it changes it to that value.
From what i've read i understand that this would be done through SQL Profiler? I'm still not sure as when i display all events, it doesn't seem to be granular enough to trace specific tables or columns.
I was able to get a trace to log logins and user transactions, but it uses the default template so not much was changed.

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

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

salkobi Ten Centuries Points: 1090 More actions · Answer 1

salkobi

Ten Centuries

Points: 1090

January 19, 2015 at 2:21 pm

#1771241

Bump,

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

It sounds more like you want "auditing" than a trace.

--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)

salkobi Ten Centuries Points: 1090 More actions · Answer 3

I'm sure there's more than 1 to do it, but yes we want to be able to audit this data and also update it where those constraints fit.

Ex, if an OrderID with company A has a destination going to x1 to y1, then the office account on it is Z. At the same time, if company A has a destination from x1 to y3, then nothing happens. Whenever it's x1 to y1 then the account should be Z, otherwise it's left alone and is not audited.

Lowell SSC Guru Points: 323518 More actions · Answer 4

profiler/trace/extended events tell you what commands were executed, but not what data changed.

an audit can tell you that a value changed.

the best way to handle it is of course at the source of the change itself...

since there is already a job that is doing the changes in your tables, why can you not add the additional logic to that job?

if you need to handle it after the fact, you need to have the ability to compare old vs new values, which means a trigger, or Change Tracking + a history table or Change Data Capture(enterprise editing required)

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!

salkobi Ten Centuries Points: 1090 More actions · Answer 5

Maybe it will help if i explain it this way.

In terms of what comes first, i would need a job to run periodcally ( Every 10 minutes or so) that will update the data(Orderheader) based on the description i posted above. After the fact, i want the same job to insert those rows in a table format that would get backed up and logged with all other tables. There would likely be relationships( I haven't gotten to that part yet...) between this new table and existing table so we could pull reports with that info.

I'd like to try creating the update job myself, but i don't have many options concerning logging or temp tables, so i'm not sure what the best approach would be.

EDIT: Sorry, just re-read the last part of your post; That sounds exactly like what i need.