Hi Tim & Team,

Is there a way to log just the fields that have changed using a TRIGGER?

Just wondering in terms of a log table, I need to log just the fields that have been UPDATED in a record for a particular table. The record contains about 60 fields and only a few will ever be updated at any given time. Is there an easy way to do that without comparing NEWROW / OLDROW for every field?

I also want to log the USERNAME, whats the best way to grab that info?

Cheers!

Philip

>Is there a way to log just the fields that have changed using a TRIGGER?
>
>Just wondering in terms of a log table, I need to log just the fields that have been UPDATED in a record for a particular table. The record contains about 60 fields and only a few will ever be updated at any given time. Is there an easy way to do that without comparing NEWROW / OLDROW for every field?

There wassn't when I wanted it

>I also want to log the USERNAME, whats the best way to grab that info?

CURRENT_USER

Roy Lambert [Team Elevate]

Thanks Roy, I guess I'll have to look at another way of doing it. Probably either from the session component or from the screen containing 60 fields itself.

Philip


I have several times built logging into an application. I don't think I've ever used it

Roy Lambert

Yeah this is for an Event system where various users are logging in and changing event details which have a lot of people involved in the event, and so any changes to certain fields could be devastating and so knowing who changed what means they can go back to that user and question them, reasoning etc.

Philip


It would mean more coding on your part but it might be faster to just copy the entire record before changes are made plus a couple of extra fields for user and time. If there's a query show a screen with the whole audit trail ordered by time.

Doing it that way means one insert for each firing of the update trigger rather than a record for each field changed which is what I'm guessing you'll be planning on doing.

Roy Lambert

I am taking a different direction to solve the problem, but if you are able to think of adding a TClientDataset at the application level it has fabulous properties to pick out only the changed fields. I built a component to do this back in DBISAM days & still use to manage change-log stuff.

Another way: I haven't fully explored the PUBLISH, SAVE UPDATES & LOAD UPDATES mechanisms in EDB ... I just use them. I know that they work by keeping a log of changes. If you set a PUBLISH on your database but limited to only the 1 table you are trying to log then the contents of the UPDATES (which I believe is held in a config table) would contain exactly the changes you need.

Finally ... are you sure you should have all your 60 fields in 1 table? My preference is to keep my tables smaller, even if there are identity relationships between them. For example I have People + Staff + Customers. The people table just contains FirstName, LastName. The Staff database contains things like StartDate etc. By dividing the larger entity into smaller tables tracking updates & logging changes can be done in a more focused way.