Tracking changes

[Middletree]

I have asked this here before, but am still trying to decide what's best,
and would appreciate the input of seasoned Asp developers.

It's an ASP-built Intranet app, so I can't show you the site, but I'll show
you screen shots if you think that will help. The app is to keep track of
issues called in to a Support help desk. These issues are called tickets.
Each ticket has one and only one row in this given table (let's call it
Ticket). Any additional info on a ticket will be entered into another table
(called History), so it's possible to have many rows in that table for each
one in the table I am focusing on.

The app in question uses SQL Server 2000 as its datasource.
The table contains info entered when a ticket was created, but I have
included a mechanism for users to go back and change info that was
originally entered. This leads me to my dillemma. The boss wants me to find
a way to keep up with changes to that original info. It seems logical to
keep this info in another table. Let's call this new table Audit.

I have explored using a trigger which would create a row in the Audit table
whenever an Update was used on the Ticket table. The problem with that is,
you have one row per changed field, and that could add up. Actually, it's
not a problem, just something to consider.

The alternative is to have ASP code which would, when any edits are made,
manually compare each new value in the form to each existing value, and
record those items which have changed. This seems like it would put a lot of
work on the web server. There are generally maybe 10 people using this app
at any given time, so that may or may not be important.


For the record, the Ticket table has about 40 fields. The PK is a single
field, and it's an Identity with type int. Most of the other fields are int,
as they contain FKs that point to values contained in statics tables. There
are a few tables of type varchar, and 3 fields which contain large amounts
of data, as they are description fields. Users type in up to 3000 characters
here, but it's typically a few hundred characters.

Besides keeping track of this stuff, I am going to have to consider that I
will have to display this stuff, as well.

I'd appreciate the input of those here who might have encountered such a
thing.

 

[Larry Bud]

I have asked this here before, but am still trying to decide what's best,
and would appreciate the input of seasoned Asp developers.

It's an ASP-built Intranet app, so I can't show you the site, but I'll show
you screen shots if you think that will help. The app is to keep track of
issues called in to a Support help desk. These issues are called tickets.
Each ticket has one and only one row in this given table (let's call it
Ticket). Any additional info on a ticket will be entered into another table
(called History), so it's possible to have many rows in that table for each
one in the table I am focusing on.

The app in question uses SQL Server 2000 as its datasource.
The table contains info entered when a ticket was created, but I have
included a mechanism for users to go back and change info that was
originally entered. This leads me to my dillemma. The boss wants me to find
a way to keep up with changes to that original info. It seems logical to
keep this info in another table. Let's call this new table Audit.

So you're keeping track of changes to the parent table? What kind of
changes can happen? Have you considered locking down certain fields
depending on the status of the ticket?

How many tickets a day are generated? How many times is a typical
ticket changed?

 

[middletree]

So you're keeping track of changes to the parent table?

Yes

What kind of
changes can happen?

Mostly varchar (less than 50 characters) or int fields (FK to another
table). Specifically, these would be changes to the data that was originally
entered. A customer calls in and says this problem is occuring at several
branches, so we check the box that says "multiple branches", then as we work
through the problem, we find that it only affect one branch. Stuff like
that.

Have you considered locking down certain fields
depending on the status of the ticket?

We want to leave open the possibility to change this info.

How many tickets a day are generated?

Usually less than 10.

How many times is a typical ticket changed?

Usually not at all. But when it does, we want to have a record of it.

 

[Larry Bud]

Mostly varchar (less than 50 characters) or int fields (FK to another
table). Specifically, these would be changes to the data that was originally
entered. A customer calls in and says this problem is occuring at several
branches, so we check the box that says "multiple branches", then as we work
through the problem, we find that it only affect one branch. Stuff like
that.



We want to leave open the possibility to change this info.


Usually less than 10.



Usually not at all. But when it does, we want to have a record of it.

FWIW, I'd just make another entry an identical table that keys back to
the original.