aspose file tools*
The moose likes JDBC and the fly likes Using a Trigger to Calculate a Value (SQL Server) Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Using a Trigger to Calculate a Value (SQL Server)" Watch "Using a Trigger to Calculate a Value (SQL Server)" New topic
Author

Using a Trigger to Calculate a Value (SQL Server)

Corey McGlone
Ranch Hand

Joined: Dec 20, 2001
Posts: 3271
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


SCJP Tipline, etc.
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30965
    
158

Corey,
Having the business logic on the database isn't a disadvantage when you have two or more clients pushing data. The alternative would be having two copies of the business logic (even if Palms could do it.)

Database tie in for one stored proc doesn't worry me. How often do you change databases? The effort of rewriting the stored proc is less than migrating everything else.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Corey McGlone
Ranch Hand

Joined: Dec 20, 2001
Posts: 3271
Originally posted by Jeanne Boyarsky:

Database tie in for one stored proc doesn't worry me. How often do you change databases? The effort of rewriting the stored proc is less than migrating everything else.


Honestly, we've been using the same database for years and I don't see us leaving SQL Server for something else any time soon.

What I'm more concerned about is the use of a trigger in the way that I'm doing it. It just seems like it would be remarkably difficult to debug - it's a bit of code that gets fired on its own and, unless you really know what's happening, you'd never know it was going. Basically, unless you're very familiar with the system, you'd look at this compliance field in the database and wonder how the heck it got in there. I guess that concerns me more than anything else.

But I haven't been able to come up with a better solution. We have multiple data feeds and, like you mentioned, having two copies of the business logic isn't appealing, either.

I guess I'll stick with what we have, here. I just wanted to bounce it off some other folks to see if anyone had a better idea that I hadn't considered.

As always, thanks for the reply, Jeanne.
 
Consider Paul's rocket mass heater.
 
subject: Using a Trigger to Calculate a Value (SQL Server)