November 21, 2019
Hot Topics:

Consider Database Triggers in Your .NET Enterprise Application Design

  • September 22, 2005
  • By Scott Robinson
  • Send Email »
  • More Articles »

I remember a time (a time long past) when the idea of tying applications together across functional (and, heaven forbid, departmental) boundaries was not only politically incorrect to the in-house user community, but downright frightening to some—and certainly intimidating to IT (or, as we called it back then, 'data processing'). The problem, from a business process standpoint, sprang largely from the fact that each department felt a strong sense of ownership of its systems—"That's our data, not yours!"—and the efficiencies of sharing it were not immediately clear, because 'data processing' couldn't really integrate processes for a price those departments wanted to pay. 'You want to see our transactions? Sure, fill out a request, we'll send you a printout ...'

Nobody in their right mind would stand in the way of business process integration today, when stripped-down lead times and in-house efficiency are mission-critical. This is especially true in the world of the Web, where the consolidation of information for Internet consumption often makes data-sharing a must. In the early days, this literally meant "messaging"—one process letting another process know that data for that process was available—and to some degree, that mindset still lingers. But, in today's business climate, there's no time in our process integration for digital courtesies. What's needed is messaging that puts data in your face: don't just say what's happening, go ahead and do what comes next! Don't just give me a status, give me the data I need to keep things moving!

Dialogs Among Applications, Systems, and Business Partners

The word "enterprise" covers a lot of territory. It can be applied to the integration of a company's in-house business processes; to the integration of processes, and sharing of data, between partner companies; to the distribution of common functionality for a broad user base (read, an Internet user base) across a widely-distributed array of resources. These scenarios and others all share several characteristics:

  • Isolation of business logic beyond the presentation and data layers of applications (that is, keeping business logic away from both the client applications and the data sources)
  • The need for applications to function across distributed resources
  • Significant communication/integration between applications
  • A high degree of scalability, driven in part by the loose coupling of one process to another (often through asynchronous messaging).

In practical terms, this means that any particular application in a broader process must:

  1. Communicate its progress and/or result to other applications, often by handing off data.
  2. Interact with other processes in asynchronous fashion, so that failure at any particular point doesn't bring everything grinding to a halt.
  3. That 1 and 2 be accomplished in such a way that significant increases in user traffic or data transfer don't compromise the system (by extension, all of this applies as much or more to processes shared between companies).

Messaging is the manifestation of all this conversation. It comes in many forms—notifications, isolated data elements, XML instances, SQL result sets, entire tables—but it comes down to one process saying to another, "Something's going on that you need to know about and/or work on—here's what you need to know and/or work with." This can be the passing of data out of one application into another; the setting of switches; notification that one application has completed, followed by the execution of another; alerts that environmental changes have occurred that will affect running services; or heads-up that certain activity thresholds have been reached. (And these are just a few of many scenarios.)

They all have this in common: They are all about processes, and all about triggering changes in the behaviors and/or responses of applications.

Letting the Database Drive

There's one place where process triggering primarily occurs: the user interface. This is the first thing that occurs to most designers, the most obvious choice, the traditional choice. 'If an event beyond the current process needs prompting, this user's activity will prompt it' is the thinking. But, this really runs counter to the whole point of enterprise integration, that the processes themselves do the talking, with as little user dependence as possible. That leaves two more places to put process triggering: the business logic layer and the data layer. Although is possible and sometimes appropriate to place process triggers in the business logic layer, it's primarily abstract and facilitative, and not our first choice. What about the database itself?

Initiating downline business processes from the data layer would mean embedding business logic in the database, wouldn't it? And isn't that a no-no? Well, yes, it's definitely a no-no, but you don't have to put business logic in the data layer to trigger business processes from that layer.

What can be done instead is to employ unconditional triggers in the database, triggers that pass along data or messages or both with any database activity in a certain table that connects a current process to one downline, and let the downline process decide whether or not to use it. Neither the trigger nor the message (in other words, data) need to know what's up ahead—the database doesn't need the burden of the decision-making overhead. Just hand off everything, and let the downline process discard what it doesn't need.

As an example, take a hospital patient administration system. Say that a participating health care organization that lends services to the hospital wants to receive a patient master data update every time that patient information for one of its participating patients is updated. You place a trigger on the database to hand off a patient record, say in XML form, to an interface process every time a row is added to or updated in the hospital's master patient table for that organization.

If the update event—an admission, a certification, discharge, whatever—meets the peripheral health care organization's criteria, the interface passes the row on that organization's system. If it's not a qualifying event, the interface ignores it.

The binding mechanisms for such a hand-off are messages—data, status words, or switches—and queues or folders that serve as 'mailboxes' between applications or systems.

Page 1 of 2

Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Thanks for your registration, follow us on our social networks to keep up-to-date