Consider Database Triggers in Your .NET Enterprise Application Design

Developer.com content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

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.

A Menu of Enterprise Triggering Options

Now, you get to the nuts and bolts of enterprise database triggering.

Send messages to a queue from SQL Server

Among the various Microsoft technologies suitable for enterprise, MSMQ is the unsung hero. Message queuing is freely available with XP and 2003 systems, and has advantages over transferring data between systems in folders because MSMQ has all the message tracking built-in. If you send a message to another application or system via MSMQ, MSMQ will make sure it’s delivered.

How do you hand a message off from the database to an MSMQ queue? There are several steps because you can’t tell SQL Server to do it directly.

1. Create an application to do the queuing. This can be a console app, for simplicity’s sake. The app’s sole function is to drop the file into a specified queue. No logic is needed at this point; it should take whatever is handed to it and just drop it in.
2. Use the extended stored procedure xp_cmdshell to execute the console app. The extended stored procedure xp_cmdshell can do command-line application execution from a stored procedure, and it’s a simple matter to load your console app within the stored proc:

DECLARE @cmd VARCHAR(255)
SET @cmd = 'c:enterpriseappmyconsoleapp.exe '
EXEC master..xp_cmdshell @cmd

3. Create a trigger to execute the stored procedure. Execute it every time there’s an INSERT or and UPDATE or both in the table that will feed data to the downline app.

Word of warning! If you use xp_cmdshell, you need to consider security. To execute it, you need to have a sys admin role or the xp_sqlagent_proxy_account on the SQL Server where it’s used!

Note that you can use this same series of steps to write to a file in a folder, rather than to a queue, if you like—but there’s a less-complicated way to get data into a file in a folder. Read on.

Use OSQL to write to a file. Ever used osql? It’s a nifty command-line program that runs SQL statements. It saves you a step when compared to the technique described above.

1. Create an osql statement that generates the data you want to pass to the next application. This is simple to do. The command line format for such a statement is:

osql  /U  /sa  /P  {password, if desired}  /d  {directory}
      /S  {server}  /Q  {the SQL statement that retrieves the
                         data you wish to hand off}
      -o  newmsg.txt

2. Execute that statement via the extended stored procedure xp_cmdshell, as before:

DECLARE @cmd VARCHAR(255)
SET @cmd = 'c:enterpriseappmyosql.bat '
EXEC master..xp_cmdshell @cmd

Or

Condense steps 1 and 2 by embedding the entire text of the osql statement into the @cmd, rather than storing it as a .bat. That is,

SET @cmd = 'osql  /U  /sa  /P  {password, if desired}
    /d  {directory}  /S  {server}
    /Q  {the SQL statement that retrieves the data you wish
         to hand off}  -o  newmsg.txt'
EXEC master..xp_cmdshell @cmd

3. Create a trigger to execute the stored procedure. Same as before.

Use BCP to copy a result set to a file. This may be the option to consider if you’re passing a new or updated table to another application or system. In this variation, a stored procedure fills in a table, and a trigger initiates BCP to export the table—and the export is to a target folder that is a hand-off to the receiving app or system. A disadvantage of this approach is that the table then needs to be cleared.

Conclusion

With these three techniques alone, you have enough tools in your enterprise toolkit to come up with various ways of tying applications and systems together with data-layer triggering mechanisms. Mix and match if you like, but don’t fail to capitalize on these inexpensive solutions to a core enterprise design problem!