I'm working on a project (
J2EE web app) in which I want to record a record to a database and, based on some of the fields in that record, the record is said to be either "compliant" or "not compliant". As it's based on the data in the record, this is essentially a calculated value, but it's not quite as simple as summing a bunch of fields. It's more like "IF A AND B AND ((C AND D) OR !C)". Don't get caught up in the logic of that - I'm not even sure I got it right. ;) But my point was that calculating whether or not a record would be considered compliant is not trivial and there is set business logic behind it. As such, I'd like to have that business logic in one place.
My initial thought was to calculate the compliance in the business logic of the web application prior to storing the record in the database. This would require me to store a calculated value, but it would make generating reports and such far easier, so I figure it's worth it. This seemed to be a great idea, but I ran into a bit of a snag.
There are actually
two sources of data for this application: the web application and Palm handheld devices that will be pushing data directly into the database. While it's rather trivial to calculate the compliance within the
Java web application, the software being used on the Palm devices isn't capable of doing that (at least I don't believe it is). That leaves me with only one point of control on the data - the database.
So to accomplish my goal of calculating compliance in one place and then storing it in the database, I've created a trigger on my database table that looks something like this:
After some very initial
testing, this does seem to work. Nonetheless, I find it horribly ugly. It ties me to MS SQL Server because I'm using "INSTEAD OF INSERT" triggers and it's taking some of my business logic out of my application and storing it in the database. However, given the constraint of the second data input (the Palm devices), I'm not entirely sure what would be a better solution.
So, really, after all that, I'm wondering if anyone else can come up with a better solution.
Any ideas?
Thanks,
Corey