Database triggers and Application Express
On the use or not of database triggers. I've held a view that triggers have a narrower scope than many in the Oracle world. This view has been challenged in my use of Apex. Triggers have some significant limitations.
- While Oracle allows multiple triggers of the same type to be created, it's not possible to control the order of the firing of triggers. Why does this matter? If you are integrating to an existing system that has triggers, your trigger might fire before the existing trigger, or after. No assumptions can be made.
- Triggers are quite difficult to debug.
- Triggers can be disabled thus bypassing all trigger logic.
- If integrating to an existing system, you may not have access / privileges to create triggers.
Some have said these "limitations" are contrived / atypical. That may be the case, my background is in Oracle Education (originally) and as a trainer I learned all of the capabilities and limitations of triggers. My role is to provide all the information and let the user / developer make up their own mind.
The advantage of a trigger, similar to database constraints, is the data is guaranteed to be maintained as there is no way to get past a trigger with dodgy application code (as long as the triggers are always enabled). This certainly has merit. Keep the logic as close to the data as possible. If you like triggers, use them.
Besides my years in Oracle Education I've done a LOT of application development, in Cobol, in PL/SQL (remember the Oracle pl/sql cartridge), in MS/Visual Basic, in Perl and mostly in Java developing ajax web apps. My preference is to have the application provide as friendly of an interception of errors as possible while still using database constraints to ensure the data is guaranteed to be maintained as intended. This means validations that are in the database are also coded in the application. The error messages returned by the database are often not suitable for direct display to end users.
I also come from the agile / test driven development approach. Having all of the logic in one place (application code) with unit tests that prove the application code is doing what's expected, is a great place to be. I have used unit tests (in java) to test pl/sql package code quite successfully and could do similar for testing triggers, but that seems a lot of work just to use triggers. If you like not using triggers, don't use triggers.
- While Oracle allows multiple triggers of the same type to be created, it's not possible to control the order of the firing of triggers. Why does this matter? If you are integrating to an existing system that has triggers, your trigger might fire before the existing trigger, or after. No assumptions can be made.
- Triggers are quite difficult to debug.
- Triggers can be disabled thus bypassing all trigger logic.
- If integrating to an existing system, you may not have access / privileges to create triggers.
Some have said these "limitations" are contrived / atypical. That may be the case, my background is in Oracle Education (originally) and as a trainer I learned all of the capabilities and limitations of triggers. My role is to provide all the information and let the user / developer make up their own mind.
The advantage of a trigger, similar to database constraints, is the data is guaranteed to be maintained as there is no way to get past a trigger with dodgy application code (as long as the triggers are always enabled). This certainly has merit. Keep the logic as close to the data as possible. If you like triggers, use them.
Besides my years in Oracle Education I've done a LOT of application development, in Cobol, in PL/SQL (remember the Oracle pl/sql cartridge), in MS/Visual Basic, in Perl and mostly in Java developing ajax web apps. My preference is to have the application provide as friendly of an interception of errors as possible while still using database constraints to ensure the data is guaranteed to be maintained as intended. This means validations that are in the database are also coded in the application. The error messages returned by the database are often not suitable for direct display to end users.
I also come from the agile / test driven development approach. Having all of the logic in one place (application code) with unit tests that prove the application code is doing what's expected, is a great place to be. I have used unit tests (in java) to test pl/sql package code quite successfully and could do similar for testing triggers, but that seems a lot of work just to use triggers. If you like not using triggers, don't use triggers.
Comments
Post a Comment