• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Using a Trigger to Calculate a Value (SQL Server)

 
Ranch Hand
Posts: 3271
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Corey McGlone
Ranch Hand
Posts: 3271
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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.
reply
    Bookmark Topic Watch Topic
  • New Topic