Tracking changes to records... 2

[jgoodman00]

I am currently in the process of adding the functionality to track changes made to records in our database. The only data which will change is stored exclusively in tblInspection. I was thinking of creating a table, tblInspectionHistory, & before a user edits a record, the existing record is copied into tblInspectionHistory. Users only have access to the data via forms, so I was going to set the records to locked as standard. If a user unlocks a record, the current data is inserted into tblInspectionHistory. If the user then closes the form without making any changes a DELETE SQL statement could be used, by joining the records by ReferenceNumber & InspectionDate.

Is this the best way of doing this? What other ways are possible? James Goodman

 

[jgoodman00]

Ok, I have been playing a little, & have revised it slightly. I have now written it so that the records are unlocked. If a user edits the record, a before_update event procedure which runs a stored procedure executes. This stored procedure has one parameter value passed in (namely the reference number, thanks to terry for that one), & automatically inserts the record into tblInspectionHistory before the current data contained in tblInspection is updated. This appears to work very well, & executes in an instant.

I thought I should therefore post my findings just incase someone else finds themselves in a similar situation in the future...

I would still be interested to hear any other ways of maintaing a record history though... James Goodman

 

[balves]

Instead of doing it at the application level, you could do it inside of the database with Triggers.

On update of a record, you could write the original record to the history table. The inspection table would have the current version of the record and the history table would have the previous versions of the record.

 

[RickCole]

I agree with balves triggers is the option I would use. All our audit trails on our databases are controlled by triggers.

This also then traps any changes which are not done via the application and via another method i.e. Query Analyzer.

Rick.

 

[jgoodman00]

How would you carry this out on the edited? (The primary key is RefNo) James Goodman

 

[jgoodman00]

should have been a 'record' at the end of that sentence!!! James Goodman

 

[RickCole]

Have a look at the following link as this explains how triggers work and are used and also gives examples.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_7eeq.asp

Rick.

 

[jgoodman00]

I have had a look at this, & skimmed through a chapter in the 'Inside SQL Server' book. From what I see though, these triggers only work when specified columns are updated. tblInspection contains approximately 100 bit fields (corresponding to pass/fail or yes/no), & probably another 50 integer fields which reference lookup tables.
For this reason I am thinking that creating such a trigger will be almost impossible.

Am I correct in thinking this, or is there is simple example someone can give me which shows a trigger that will run on a table with n rows, & append the existing data to another table before the new data is committed to tblInspection???? James Goodman

 

[RickCole]

The following example will insert a row into the history table ADDRSS_HIS every time any record is updated in the table ADDRSS.

This is REGARDLESS of what column is updated in the table ADDRSS.

The table definition in ADDRSS_HIS is identical to ADDRSS apart from an extra field called CREATE_TS. In here goes the current date and time to show when the update took place. This extra field can come be quite useful for audit tracking.

Script up your column names from your tables and amend the trigger accordingly.


Please note this is a trigger in it's simplest form with no no error handling.

CREATE TRIGGER ADDRSS_U_TRIG ON dbo.ADDRSS
FOR UPDATE AS

INSERT INTO ADDRSS_HIS(CUSTOMER_NUM,
CREATE_TS,
ADDRESS_1,
ADDRESS_2,
TOWN,
COUNTY,
POST_CODE)

SELECT A.CUSTOMER_NUM,
GETDATE(),
A.ADDRESS_1,
A.ADDRESS_2,
A.TOWN,
A.COUNTY,
A.POST_CODE

FROM DELETED A
GO



Rick

 

[jgoodman00]

Are there any workarounds for text or ntext fields, because this table has a single ntext field (D'Oh!). I changed it to varchar (for experimental purposes) & the trigger works perfectly. However, that field really needs to be a text field, because supplementary notes are entered in it, which could be quite extensive.

Any alternatives? James Goodman

 

[RickCole]

Within the trigger you will have to add some more SQL containing appropriate UPDATETEXT commands.

Have a look in BOL under the topic UPDATETEXT.

Depending on how often this table is updated and the size of you transaction log and database you may want to pay special attention to the following:

WITH LOG
Specifies that the inserted text, ntext, or image data is logged. This option allows recovery, but it can quickly increase the size of the transaction log.

Note If the WITH LOG option is not specified, the database must have the select into/bulkcopy database option turned on. For more information, see sp_dboption and Setting Database Options.

Also if nonlogged operations occur within a database, you would normally want to then do a full database backup.


You might want to consider putting some criteria into your trigger to maybe put you text data to a separate table and then only when the text data is updated.

Rick.

 

[jgoodman00]

I have been reading about this a bit. Would an INSTEAD OF trigger be better?
However, it seems an INSTEAD OF trigger would be much longer to write, because from what I understand you need to write an INSERT statement for the old data (stored in deleted?) & a seperate UPDATE statement for the new data (stored in updated?).
Is this correct? Would this be a better way of doing it?

I am kinda worried about the way UPDATETEXT/WRITETEXT don't log their activity. These fields would be updated everytime a record is changed, so if these actions go unlogged we are going to be forever backing the database up.

Would another alternative be to do the following:

Create a seperate table which stores only this text field, say tblRemarks.
Write an AFTER UPDATE trigger to handle all of the fields in tblInspection.
Write an INSTEAD OF UPDATE trigger to act on the table containing this single text field, which would copy the record in the deleted table into its own history table (tblRemarksHistory), & update the record in tblRemarks using an update statement & the updated table.



Is that a feasible solution? Will it reduce performance significantly?
James Goodman

 

[RickCole]

I'm assuming you’re using SQL 2000, as INSTEAD triggers didn't exist on SQL Server 7.0. Have a look at the following link as this is a quite good article on using INSTEAD triggers.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag2k/html/INSTEADOF.asp

However from what I have gathered from your post's I don't think that's what you need.

I obviously don't know all aspects of how your application that updates the records will be working but my gut feeling is to have different tables. i.e. tblRemarks and tblInspection which both have simple update triggers. The performance issue of going from 1 table to 2 tables I think will be unnoticeable.

Just as an aside I never use text fields unless they’re absolutely necessary probably due to the fact they have caused me headaches in the past. If it’s just pure comments you are holding in you could use two tables but with the remark table using a large varchar field. If any comments exceed the size of the field a sequence number field in the table is used to control multiple comments. This is how we handle remark entry at our site.

Rick.

 

[Guest_imported]

In a DELETE, INSERT, or UPDATE trigger, SQL Server does not allow text, ntext,
or image column references in the inserted and deleted tables if the compatibility
level is equal to 70. The text, ntext, and image values in the inserted and
deleted tables cannot be accessed. To retrieve the new value in either an INSERT
or UPDATE trigger, join the inserted table with the original update table. When
the compatibility level is 65 or lower, null values are returned for inserted
or deleted text, ntext, or image columns that allow null values; zero-length
strings are returned if the columns are not nullable. <br>
If the compatibility level is 80 or higher, SQL Server allows the update of
text, ntext, or image columns through the INSTEAD OF trigger on tables or views.

 

[jgoodman00]

I have written an AFTER_UPDATE trigger now, & converted the remarks column to a varchar field (8000chars will be more than enough).
In our existing database, I used the Environ(&quot;UserName&quot function to log the user which was logged in at the time of any record modifications. I tried adding an UPDATE statement onto the end of the trigger, & utilising this function (so as to log who modified the record), along with the GetDate() function (to log when the record was modified.

Basically I am now happy with the creation of the historical record when a user updates a record, but i want the updated record (the record in tblInspection) to show what user was logged in, & what time/date they modified the record...
What is the favoured way of achieving this??? James Goodman

 

[jgoodman00]

I have solved this problem by using the following SQL:


UPDATE tblInspection
SET ModUser = SUSER_SNAME(),
ModDate = GetDate()


all now seems to be working well.....
James Goodman