LewisC’s An Expert’s Guide To Oracle Technology
By request, this entry will discuss some issues surrounding DML triggers. Triggers are a nifty feature. When you need them, they are very useful. They can also make maintenance and debugging an absolute nightmare.
API vs Trigger Approaches
I am an API style programmer. I do not mean that I put all of my logic in INSERT/UPDATE/DELETE procedures for each table, although that is not a bad idea. What I mean is that I tend to write functional packages and those packages deal with DML.
As an example, let’s say I have an HR system. I have a People table, a Job table, a Pay-Scale table and an Assignment table. Jobs have pay scales and the Assignment table associates a person to a job. All of the tables sequences for primary keys and all tables use database defined referential integrity.
I would have an API for Jobs. That API would deal manipulating Jobs and Pay-Scales. The insert code would select the PK sequences.
I would have an API for adding people. An Employee MUST have an assignment (that is kind of what defines an employee), so the Assignments table is also manipulated in the People API. A person can change Jobs (get a new assignment), be separated (remove the assignment), etc. If for some bizarre reason I wanted to have the Job name in the assignment table in addition to the Job PK (I don’t recommend this, it’s just an example), I would do that in the API.
So, what how does this relate to this blog topic? Not a single trigger. If there is an error anywhere, I have a couple of APIs to look at.
What would be a trigger based approach? A trigger to populate the PKs. A trigger to populate the denormalized Job name. Possibly even triggers to deal with integrity and referential constraints (but I sure hope not!).
In the trigger based approach, you now have code in many places, any of which can (and at some point will) break. In addition to the maintenance issue, there is also a performance impact with row-level trigger processing.
I would choose the former, API approach over the trigger based approach every time. I have inherited trigger based applications and suffered through every one of them. When I have time, I convert those to APIs as soon as I can.
Referential Integrity
Do not use triggers instead of foreign keys. I have seen trigger based foreign keys and the reason was that the “model was too complex for database referential integrity”. In every case where that statement has been made (at least that I have seen), it was due to a poor data model and not a limitation in the database.
Distributed referential integrity is a different beast. I have seen one case where a trigger was used to maintain integrity with data in a different database. I have to admit that in this case, I don’t see any alternatives. The trigger incurred some overhead in the application but it was an acceptable amount. If the remote database was down, the trigger failed but that was an intended effect.
Auditing
I have used Triggers for auditing database changes in the past. I even wrote a code generator that generated the triggers for me by looking at the dictionary and dynamically getting columns and such. At one time, I think that was the best approach. Today, Fine Grained Auditing offers an incredibly robust auditing mechanism. It works with SELECT statements and it also allows you to capture other information that is impossible or difficult to get with any other method.
Replication and Data Feeds
Another use of triggers has traditionally been to replicate all or subsets of data to different schemas or databases. Sometimes for application usage and other to aggregate data in a warehouse. Almost all databases now offer some kind of a database link and any database worth using offers some kind of replication.
In many cases, the database will create triggers behind the scenes but at least the database is then responsible for maintaining those triggers. Oracle offers Oracle Streams which is very robust and offers scheduled or real-time replication and Change Data Capture.
Multi-database Support
If you truly need to maintain your application on multiple databases, triggers can be useful. I don’t mean that you use a bunch of Oracle features and hope to one day migrate but you actually, actively maintain your code base to run on multiple platforms.
In this case, triggers can keep you from changing your code as often. Take the PK example of using sequences. Some databases use an auto-increment data type, other databases use sequences but the select syntax is different from Oracle’s syntax. In this case, you can encapsulate the logic in a trigger and only change that code when required. In the case of migrating to a database with auto-increment, you won’t need the trigger at all.
Also, when auditing your application, you may have specific needs that individual databases might not support. In that case, you might want to use triggers to audit. You would want to have a common audit record across database types so it would make sense to use the triggers even on databases that support advanced auditing.
Third-party Applications
If you need to alter data or flow in a third-party application, your only option might be a trigger. In that case, you really have no choice. Before you choose a trigger, think about the other available options. In many cases, Fine Grained Auditing or streams can achieve the same thing and will be a lot less intrusive to the application.
I hope this answers some of the questions about using triggers. Did I miss any cases where it really makes sense to use a trigger? Have you seen triggers used in other ways (good example or bad)?
LewisC
