| Subject | Re: [firebird-support] Create Log for tables change, which one is better via app or via firebird? |
|---|---|
| Author | Norman Dunbar |
| Post date | 2012-01-26T07:09:20Z |
Morning Sugiarto,
in the application. I'm referring to data integrity, constraints,
security etc here.
For example, your application may decide that only certain values are
allowed in a column and checks for it. This is fine. It may decide to do
other validations and this too is fine, as the user gets to see errors
and problems before they are sent to the database and a constraint
violation error comes back.
However, what happens if you decide that 4 values are now required, for
example, yo have to amend the application code, test it and roll it out
to all your users. With a constraint in the database, you have one
script to amend and run, once only, job done. (ok, you still have to
test it of course!)
Equally, if your application is logging the changes then anyone with
access to the database and the right tools, can connect and make changes
which are not logged.
Now, you have the problem on needing to write triggers for every column
in every table that you need to log changes for. This can be done with a
small bit of coding, but would it be easier to have one trigger per
table and have an audit table, perhaps in a separate database if this is
possible, where you simply write the old row to the audit table before
updating the new row?
Another consideration, do you want to store attempted changes as well?
If someone changes a column then rolls back - are you still interested?
You must put your validation and constrai9nts checking as close to the
data, where it belongs, that means you do it in the database. You can
still do checks and similar validation in the application - for ease of
user use, but the main location has to be protecting the data.
HTH
Cheers,
Norm.
--
Norman Dunbar
Dunbar IT Consultants Ltd
Registered address:
Thorpe House
61 Richardshaw Lane
Pudsey
West Yorkshire
United Kingdom
LS28 7EL
Company Number: 05132767
> I want to record all changes in tables (insert/update/delete).Always, always always do whatever you have to do in the database and not
>
> I can do this by creating triggers for each tables, triggers ex :
>
> ...
>
> Or, I can record changes via my app, which one do you thing is better, regarding database performance and size?
in the application. I'm referring to data integrity, constraints,
security etc here.
For example, your application may decide that only certain values are
allowed in a column and checks for it. This is fine. It may decide to do
other validations and this too is fine, as the user gets to see errors
and problems before they are sent to the database and a constraint
violation error comes back.
However, what happens if you decide that 4 values are now required, for
example, yo have to amend the application code, test it and roll it out
to all your users. With a constraint in the database, you have one
script to amend and run, once only, job done. (ok, you still have to
test it of course!)
Equally, if your application is logging the changes then anyone with
access to the database and the right tools, can connect and make changes
which are not logged.
Now, you have the problem on needing to write triggers for every column
in every table that you need to log changes for. This can be done with a
small bit of coding, but would it be easier to have one trigger per
table and have an audit table, perhaps in a separate database if this is
possible, where you simply write the old row to the audit table before
updating the new row?
Another consideration, do you want to store attempted changes as well?
If someone changes a column then rolls back - are you still interested?
You must put your validation and constrai9nts checking as close to the
data, where it belongs, that means you do it in the database. You can
still do checks and similar validation in the application - for ease of
user use, but the main location has to be protecting the data.
HTH
Cheers,
Norm.
--
Norman Dunbar
Dunbar IT Consultants Ltd
Registered address:
Thorpe House
61 Richardshaw Lane
Pudsey
West Yorkshire
United Kingdom
LS28 7EL
Company Number: 05132767