>Hi guys.
>
>When I came to work for my current company, I saw something that I had never seen before
>(Back then I had 5+ years of experience working with various SQL Server DBs):
>
>on each table in database there is a trigger which is fired up before updating the row.
>The trigger makes a copy of the updated row and marks it as inactive by setting field
>RECORD_ACTIVE = 0. I think that this copy-on-update model is very..very bad.
>
>There is one and only one reason of making such copy, data safety - it means that we have
>history for row changes. We are able to tell what and when had changed.
>
>Here are my reasons against it:
>1. Decreased performance when making updates. In fact, we had to introduce a check in trigger
>to not make a copy of record if we are importing and updating data via import tool because
>it worked to slow. So I think the whole point of data safety is lost anyway because we can
>not keep the data safety policy everywhere.

Reality conquers theory. Though if you keep the files you are importing, then I wouldn't say the whole point of data safety is lost, you just have to look in the imported file rather than in the database if you need to look back in history (which can be cumbersome, but the data is there).

>2. Increased table size - decreased performance when reading data.
>
>3. We have in each table the fields that saying which user( and when) introduced last change
>to the record. I think it is sufficient. Our software is not medical software or banking
>software, we do not keep so crucial data. There wont be any law suits when some user will
>accidentaly change some row. And after all, it is his duty to know what is he changing.

Maybe management should decide what to keep? One thing is small accidental changes, but what about changes done to several records due to an employee not understanding how things should be done? Or an employee trying to steal from your company (maybe delete customers because he wants to start his own competing company and contact those customers himself).

>4. Due to this design I am not able to introduce an unique constraint on a column. For
>example : often there is a need to add unique constraint on a NAME column - I am not able
>to do this because constraint will fail for the first copy of the given record. (If I
>create constraint on NAME and RECORD_ACTIVE - constraint will fail on second copy.)
>
>5. As long as I am working here there was never a need to check for the history of a record
>in any database. And we have like 100 or more clients...
>
>So guys. I am asking you for a fresh point of view. Should I go to the menagement and tell
>them that this is generally a bad idea or maybe I am wrong? Maybe this is the way it should
>be done!?

I don't think there is a generally right or wrong solution to this question, except in extreme cases where you can guarantee that history is irrelevant or know that history is as important as the present. It sounds a bit over the top to have this on EACH table in your database, e.g. if you have a COUNTRIES table, then I'd say history is probably irrelevant.

We do things slightly different that might be a bit more flexible. For our three main tables, we have AFTER UPDATE and AFTER DELETE triggers that copies the old values into other tables that have similar structure with a few additional fields. For most other tables we do not keep any history (well, except daily backups, of course). Such a solution would allow you to have a constraint on NAME in the main table, but not on the table with old values (though you might not want to constrain NAME, in many situations you could experience the same name not being unique - e.g. there are two countries named Congo). It wouldn't help speed-wise regarding imports of UPDATEs, but INSERTs wouldn't copy anything.

As for talking to your management, it might be wiser to come up with an alternative and say "I've got an idea, let's do it this way" rather than just saying "I really don't like our system".

HTH,
Set