History/Data Change File Approach

I need to record in a table:
Who, When, What Field and New Value of Fields
When changes occur to an existing record.

The purpose is for users to occassionally view the changes. They'll want to be able to see the history of the record - who changed what and when.

I figured I'd add the needed code to the stored procedure that's doing the update for the record.

When the stored procedure is called to do the update, the PK and parameters are sent.

The SP could first retain the current state of the record from the disk,
then do the update, then "spin" thru the fields comparing the record state prior to the update and after. Differences could be parsed to a "Changes string" and in the end, this string is saved in a history record along with a few other fields:

Name, DateTime, Changes

FK to Changed Record: some int value
Name: Joe Blow
Date: 1/1/05 12:02pm
Changes: Severity: 23 Project: Everest Assigned Lab: 204

How does the above approach sound?

Is there a better way you'd suggest?

Any sample code for a system that spins thru the fields comparing 1 temporary record with another looking for changes?

Thanks,

PeterHave you considered using a trigger? You can use the inserted and deleted tables to compare your data without having to save it manually. Then insert your data into the history table as you suggested.

We often just save the before image to the history table along with the type of operation performed, the id that was used to perform it and a time stamp. Inserted records are not recorded (because all their data is already recorded on the live table) but deleted ones are. The differences for updated records can be determined at any time by comparing the before image to the next or previous stored image or the current actual record. We rarely actually look at this sort of history however unless data disappears or the customer tells us that there is something else wrong with the data and we need to trace what happened to it.|||ejustuss - thanks for the thoughts.

Good idea about simply saving the before image prior to the actual save of the new one.

Our users are used to systems where they can click a button and see essentially the change history of the record. This particular system is a Work Order system. The status of the WO changes over time, etc.

In other non-SQL Server systems I've developed I have a routine that prior to actual save:

1. saves "before save" copy of record
2. updates the record with new values into the DB
3. peels off an "After save" copy of record
4. runs a routine that compares side by side each field. Any changes are noted in a text variable (field name, new value).
5. Once all the fields are spun thru (compared), if there is any information in the text variable, a "change table" record is created with FK to the parent record, Who Changed it, When changed, and a single text field describing all the changes.

Weeks later when a user is viewing the particular record, they can press a button and have a query run against the change table to bring up a simple list of what changed when.

One wrinkle is that a Work Order has a huge text area. Once a WO is accepted by a lab, this text are becomes "frozen". So if we simply peel off a before save copy each time a user specifies an update - I wouldn't want to needlessly include this particular field due to space considerations.

Bottom line - I was assuming someone might have a canned routine for spinning thru a record comparing all field values against an identical layed out record. I figured there might be a system function or 2 to:
1. Identify how many fields are in a table
2. identify the content of a field - something like @.@.Field(i)
where i=1 to number of fields in the table.

Peter|||I don't know of any function like that although I am sure you can write one.

In triggers you can use IF UPDATE(column) to test if a column is updated but you still have to use the column names which means writing a different trigger for each case . The data in text fields will probably not be accessible within the trigger.

You don't have to save all the fields if you do a before image either just the fields you want to compare in case they are updated.

History of a SQL Job Deletion

Does anyone know if there is a record kept of a deleted SQL Agent Job? I
need to track down why a job is going away but I haven't been able to
figure out how to do it.
Thanks!
--
Posted via http://dbforums.comThe SQL Jobs are all stored within the msdb database. If you have been
backing up msdb you should be able to get the job back
--
HTH
Ryan Waight, MCDBA, MCSE
"MrSanity" <member40366@.dbforums.com> wrote in message
news:3390116.1063975524@.dbforums.com...
> Does anyone know if there is a record kept of a deleted SQL Agent Job? I
> need to track down why a job is going away but I haven't been able to
> figure out how to do it.
>
> Thanks!
>
> --
> Posted via http://dbforums.com|||Are you saying that some of your jobs just Disappear ?
If yes then have a look at the job properties. On the Notifications
tab there is a "Automatically delete this job - When it
fails/Succeeds/completes"
Do you have that checked ?
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org|||There is no record kept of deleted jobs in SQL Server. If your msdb database
uses either the full or bulk-logged recovery model and you have backups of
the transaction logs, you can use LogExplorer from www.lumigent.com to do
research.
If a job disappears on a regular basis, make sure that you don't have the
'automatically delete job' property set. You can find it on the
Notifications tab of the job properties in Enterprise Manager.
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"MrSanity" <member40366@.dbforums.com> wrote in message
news:3390116.1063975524@.dbforums.com...
> Does anyone know if there is a record kept of a deleted SQL Agent Job? I
> need to track down why a job is going away but I haven't been able to
> figure out how to do it.
>
> Thanks!
>
> --
> Posted via http://dbforums.comsql

High volume batch insert

Hi,
I need a SQL program to insert a record into a parent table and then up
to 200 child records, using the primary key of the parent record. Whats
the best way to structure this procedure for performance and
reliability ? e.g
INSERT INTO tblExam ( dteExam,intModule,) Values () ...
SET intExamID= @.@.IDENTITY
' Psedo-code
For each Candidate in frmExamRequest
INSERT INTO tblCandidateExam ( intExamID, intCandidateID )
Next
The data is all coming from a form on an ASP page, I could break it
into two forms but for the users a single form with exam date, and list
of candidates they can check/uncheck is much easier.
If I have 2 stored procs to insert a exam instance & candidate
instances, the latter proc will be called 200 times over an ADODB
Connection. Is it possible to have a single stored proc to do the whole
task in a transaction with unknown number of candidates ?
thanks.When the user hits "Enter" you could either collect all the candidate IDs an
d
put them in a comma delimited string and then pass that string to the stored
proc that's doing the insert.
You could perform inserts into tblCandidateExam by looping through the
candidate IDs.
I think that's probably the easy way. Hope that's helpful
Cheers,
mmm
"cc900630@.ntu.ac.uk" wrote:

> Hi,
> I need a SQL program to insert a record into a parent table and then up
> to 200 child records, using the primary key of the parent record. Whats
> the best way to structure this procedure for performance and
> reliability ? e.g
>
> INSERT INTO tblExam ( dteExam,intModule,) Values () ...
> SET intExamID= @.@.IDENTITY
> ' Psedo-code
> For each Candidate in frmExamRequest
> INSERT INTO tblCandidateExam ( intExamID, intCandidateID )
> Next
> The data is all coming from a form on an ASP page, I could break it
> into two forms but for the users a single form with exam date, and list
> of candidates they can check/uncheck is much easier.
> If I have 2 stored procs to insert a exam instance & candidate
> instances, the latter proc will be called 200 times over an ADODB
> Connection. Is it possible to have a single stored proc to do the whole
> task in a transaction with unknown number of candidates ?
> thanks.
>|||See if one of these helps:
http://www.aspfaq.com/show.asp?id=2248 Arrays & Lists
http://www.sommarskog.se/dynamic_sql.html Dynamic SQL
http://www.users.drew.edu/skass/sql...bleProc.sql.txt List to
Table script
Andrew J. Kelly SQL MVP
<cc900630@.ntu.ac.uk> wrote in message
news:1117472922.543985.50420@.g14g2000cwa.googlegroups.com...
> Hi,
> I need a SQL program to insert a record into a parent table and then up
> to 200 child records, using the primary key of the parent record. Whats
> the best way to structure this procedure for performance and
> reliability ? e.g
>
> INSERT INTO tblExam ( dteExam,intModule,) Values () ...
> SET intExamID= @.@.IDENTITY
> ' Psedo-code
> For each Candidate in frmExamRequest
> INSERT INTO tblCandidateExam ( intExamID, intCandidateID )
> Next
> The data is all coming from a form on an ASP page, I could break it
> into two forms but for the users a single form with exam date, and list
> of candidates they can check/uncheck is much easier.
> If I have 2 stored procs to insert a exam instance & candidate
> instances, the latter proc will be called 200 times over an ADODB
> Connection. Is it possible to have a single stored proc to do the whole
> task in a transaction with unknown number of candidates ?
> thanks.
>