> Also, add a check to the trigger so it only performs the copy under
> "normal" conditions. For example, you might perform all your importing
> using a login that employs the IMPORTER role. Then the trigger can
> check whether the current connection is using the IMPORTER role and
> not perform the copying if it is.
>
>This is what I am currently doing except that copy is made to the same
>table under normal conditions. But as I've said earlier - does introducing
>the IMPORTER role(assuming that with this role records are not copied on
>update) not kill the original purpouse of data-safety? It is hard to see
>for me the difference between updating a record by user mistake in
>application and updating the record by user mistake that he made picking
>up the wrong(old data) import file.
>
>In first case I have the copy of a record, in a second case I do not have.
>That is inconsistent.

The way you've described your update process, you do the copy of the NEW record when doing an update/insert. Hence, the one thing that you do not get into your table, is the copy of the wrong, old data. Sure, it will be confusing when there's two changes to a record and your user only made one change, but if you have some information about when which file is imported (not in the individual record) and notices that there was an import between the old and current version of the record, then you could in theory look at the imported file to find the change that wasn't stored as a change.

My point is that whilst it makes things more difficult when individual records are updated in batches without storing the individual change, it is still theoretically possible to trace the changes unless you delete the batch file. If you didn't record changes done manually by a user, then it would be impossible to trace those changes.

The way I wrote (yesterday) that we do logging by copying the OLD values into another table when updating, means that having an IMPORTER role that circumvents this scheme wouldn't work - the things you would not log would be the change immediately BEFORE the import, the import itself would be logged the next time changes were done manually. So, if you need an IMPORTER role that doesn't copy anything, I think this is a valid reason for making copies of the NEW record rather than the OLD.

Though the ideal solution would normally be to also log changes done by the IMPORTER role.

Set