ckaminski.com

Triggers in Complex Business Applications

Developing business logic in n-Tier applications can be difficult. Sometimes it is best to store a moderate amount of application logic in the data tier. SQL offers us constraints for data validity, and it offers us triggers, tools which allow us to enforce discrete business rules no matter what we do in our logic tier. What follows is an experiment in triggers for developing applications.

Nearly every SQL instruction manual teaches what I consider the most common introduction to transaction consistency, the bank transaction. Consider a bank withdrawl:

 Account 101:  $9000.00  
   Check 1023, $2059.00

A decent first attempt at this calculation would look something like this:

 begin trans
 insert into transactions values('#1023', 2059.00) 
 update accounts set balance = balance - 2059.00 where account = 101 
 commit trans

Transactional consistency is a great thing. This is, in fact, the prototypical example given in every SQL 101 class. It ensures that the transaction is recorded, and that the current balance is updated properly.

In this case, however, I feel that the SQL language provides a much better mechanism for accomplishing this operation: triggers. The limitation of the prior methodology shows when performing maintenance. When a transaction has to be rolled back, the software must accomodate the balance update. A better idea is to let triggers do our work for us. An example follows (pseudo code, since many RDBMS SQL languages differ greatly):

 create trigger account_balance_update 
     on transactions 
  after insert, update, delete 
 AS
   
 declare @count int 
 select @count = rowcount from inserted 
 if @count > 0 begin
 end 
 else
 
 ....
  Add code
 ....

GO

This trigger tests the updated data for zero balance, and will support rollback if an attempt is made to withdraw too much funds. Functionality can be added to the transaction table to deny ATM withdrawals if the balance would drop below zero, but allow checks to clear depending on complex rules based on credit rating and past banking history.

Then when you need to update or perform any transaction, you simply execute it. No need to duplicate code in numerous modules, no need to concern oneself with numerous instances of possible error-prone code duplication. More importantly, building logic such as this around triggers prevents database inconsistency from users deleting or modifying records in the database using tools like SQL*Plus or SQL Server Query Analyzer.

Last Update: Mon Jul 16 19:15:11 2007