Triggers in Database
- Thread starter sampath
- Start date
So how many more writes are there per record now? And what is the system process actually doing? Have you checked that everything is using suitable indexes? Have you benchmarked reads before and after the changes? Have you debugged the code to see what is actually happening?
RealHeavyDude
Well-Known Member
There is always a performance trade-off when introducing something like auditing. Depending on your solution it will produce many more database writes and you might even need to tune the database broker to cope for that.
Plus, you need to be aware that the WRITE and DELETE database triggers do fire on the end of the buffer scope. Therefore they might not fire when you expect them to fire and produce inefficient database writes. When database triggers are involved in your transaction you need to carefully design the transaction and buffer scope for every database update.
Furthermore, you should re-think your design and ask yourself: Is it really necessary to audit ALL tables. I know that is usually what people say. But if you ask them for more details most likely you'll end up with a requirement to audit only a handful of tables which hold sensitive data. Most likely there even won't be a high transaction load on such tables too. Usually I experience people switching everything on and then they start to wonder where enormous database growth and performance issues come from.
Personally I don't like database triggers - not one bit. For one, they hide business logic in place where you might not expect it. Plus, they can be disabled with a simple ABL statement ( have a look at the DISABLE TRIGGERS statement ) and they do not fire when the database is updated via the SQL engine. Furthermore, depending on your implementation, they might additionally introduce performance issues.
If I were you I would have a look at the auditing that is built in to the database engine. It is policy based and rather easy to implement without a need to change the existing application.
Heavy Regards, RealHeavyDude.
Plus, you need to be aware that the WRITE and DELETE database triggers do fire on the end of the buffer scope. Therefore they might not fire when you expect them to fire and produce inefficient database writes. When database triggers are involved in your transaction you need to carefully design the transaction and buffer scope for every database update.
Furthermore, you should re-think your design and ask yourself: Is it really necessary to audit ALL tables. I know that is usually what people say. But if you ask them for more details most likely you'll end up with a requirement to audit only a handful of tables which hold sensitive data. Most likely there even won't be a high transaction load on such tables too. Usually I experience people switching everything on and then they start to wonder where enormous database growth and performance issues come from.
Personally I don't like database triggers - not one bit. For one, they hide business logic in place where you might not expect it. Plus, they can be disabled with a simple ABL statement ( have a look at the DISABLE TRIGGERS statement ) and they do not fire when the database is updated via the SQL engine. Furthermore, depending on your implementation, they might additionally introduce performance issues.
If I were you I would have a look at the auditing that is built in to the database engine. It is policy based and rather easy to implement without a need to change the existing application.
Heavy Regards, RealHeavyDude.