Sunday, May 18, 2008

Database Triggers, Encapsulation and Composition

When Object Oriented Programming burst into the mainstream, encapsulation rapidly came to be accepted as a basic tenet of sound programming. Modern database servers provide a wonderful way to tightly couple code and data, a way that is secure, non-subvertible, flexible and powerful. This week we will see the first use of the database trigger, and examine its curious ability to accomplish the same goal as OOP encapsulation. Furthermore, while the database requires no analog to the idea of inheritance, triggers also serve as a very powerful way to build up business processes through composition.

There are links to related essays on normalization and denormalization at the bottom of this post.

This blog has two tables of contents, the Topical Table of Contents and the list of Database Skills.

Triggers 101

A "trigger" is a small computer program stored on the database server that fires whenever a table is written to. The trigger can validate and if necessary reject the operation, and it can also modify the data being written. These two simple abilities can be combined to implement incredibly powerful operations and combinations of operations.

Before we get to the first example, we need to review the four basic options that are available when you define a trigger. These four options will be used in the examples below, and this quick review can serve as a reference when you review the examples.

  • INSERT UPDATE and DELETE Triggers can be defined for each of these operations, and most databases allow you to assign define a trigger to fire on combinations, like INSERT and UPDATE but not DELETE. Most servers also allow you to define multiple triggers to fire on each event.
  • BEFORE or AFTER. A trigger can be defined to fire before or after the operation. Triggers that fire before an operation can modify the data being written or reject the operation. Triggers that fire afterwards are good for writing to history tables.
  • STATEMENT or ROW. Some SQL statements such as DELETE and UPDATE affect multiple rows. All popular servers (that I know of) fully support having a trigger fire on each row of the operation. It is sometimes desirable for performance reasons to fire a trigger only once, either before or after the rows are affected. Support for this ability varies widely in the real world.
  • SECURITY. Security models vary widely in the wild. My favorite amongst the servers I have used is Postgres, which lets you grant all users complete freedom to fire any trigger. This is so useful because it lets you define security just once in terms of table access, and since triggers fire on table access, the effective trigger security shows up for free.

The First Example: Denormalization Patterns

Three weeks ago I reviewed the three basic Denormalization Patterns that are used to put calculated values into a database. These denormalization patterns fit hand-in-glove with triggers. The following code shows three simple calculations that can be performed in a trigger. I should note that the syntax is peculiar to Postgres, and you will need to review your own server's documentation to get the syntax right. If you have never written a trigger before, I should further warn that Postgres has a perverse two-step process, it makes you write a function and then attach that function to a trigger. MS SQL Server lets you define the trigger and code together in one step, which makes much more sense. But that being said, here it is:

CREATE FUNCTION order_lines_before_row_func RETURNS TRIGGER AS
$BODY$
   -- execute the FETCH of the price
   SELECT INTO new.price price FROM items 
    WHERE items.sku = new.sku ;
    
   -- EXTEND The price
   SET new.extended_price = new.price * new.qty;
   
   -- AGGREGATE the total to the sales order
   UPDATE ORDERS set lines_total = COALESCE(lines_total,0) + new.extended_price;
$BODY$
SECURITY DEFINER LANGUAGE PLPGSQL;

-- This second step is a peculiar requirement of postgres, 
-- by contrast, MS SQL Server would have let me define these options
-- above and would not require this very annoying second step.
CREATE TRIGGER order_lines_before_row_trigger 
BEFORE INSERT OR UPDATE
    ON ORDER_LINES
   FOR EACH ROW
EXECUTE PROCEDURE order_lines_before_row_func;

It is no understatment to say that you can build huge applications of hundreds of tables that use little more than the example above repeated over and over across many tables. There is of course more to the story, lots more, but the example above lays the pattern for most of what follows.

The Second Example: Playlist Ordering

Some months ago a member of the Long Island PHP User's Group asked about a method for ensuring that an ordered playlist would never have two songs in the same position. This question struck me because it is a great example of the general problem of maintaining any ordered list when you may be moving items around. It turns out an extremely simple trigger takes care of it neatly.

CREATE FUNCTION playlist_before_row_func RETURNS TRIGGER AS
$BODY$
   -- given a column "position", if an item is given a position,
   -- already occupied by another row, bump that other row forward
   UPDATE playlist SET position = position + 1
    WHERE position = new.position;
$BODY$
SECURITY DEFINER LANGUAGE PLPGSQL;

-- This second step is a peculiar requirement of postgres, 
-- by contrast, MS SQL Server would have let me define these options
-- above and would not require this very annoying second step.
CREATE TRIGGER playlist_before_row_trigger 
    ON playlist
BEFORE INSERT OR UPDATE 
   FOR EACH ROW
 EXECUTE FUNCTION playlist_before_row_func();

This trigger works because triggers will cascade, which is explained next.

Some Notes on Cascading Triggers

A trigger can contain code that executes an UPDATE statement, which may then fire other triggers, which themselves contain UPDATE statements, and so on. This is called cascading.

The second example above only works if a trigger can cascade. The chart below shows how it works. The first column shows the value of "position" for a few rows. Imagine the user inserts a new row at position 1, "stepping on" the value that is already there. The trigger shown in example two takes care of everything:



- POSITION -
 Old | New | Trigger action
-----+-----+------------------------------------------------------ 
     | 1   | Insert causes trigger to fire the UPDATE
  1  | 2   | UPDATE flips this from 1 to 2, causing trigger to fire again
  2  | 3   | 2nd UPDATE flips this from 2 to 3, causing the trigger to fire again
  3  | 4   | 3rd UPDATE flips this from 3 to 4, trigger fires a 4th time
  6  | 6   | Fourth UPDATE finds no target row, execution ends, no action on this row
  7  | 7   | no action

This patterns has some latent performance issues that are too complex to get into until I can do a complete essay on this table pattern. But the point here is that those performance problems are a part of the pattern itself, and using a trigger for the pattern is actually the fastest way to implement the pattern.

Encapsulation, Subversion and Security

The term encapsulation is used (as far as I know) exclusively by Object Oriented theorists. Nevertheless, it is striking how well the term describes the effect of triggers. When a trigger is put onto a table, you have the tightest possible integration of code and data, at least as far as database applications are concerned. This is true because the trigger allows for non-subvertibility and integrated security.

A system is said to be "subvertible" if a program or user can, by accident or malice, disobey a business rule or circumvent any of the protections set up in the system. Triggers represent a non-subvertible implementation of business rules, because no application code or user can bypass their actions. If that is not encapsulation I don't know what is.

There is a really nifty advantage to triggers that falls into this general discussion. When you implement calculations in triggers, you can freely allow access to the database from outside of your own application. This is a crucial feature when selling to companies with large IT departments that expect this ability like a birthright. Going further along these lines, you can implement browser and desktop versions of the application without recoding all of the calculations.

Last week we had an Introduction To Security in which we saw that database security is implemented as restrictions on who can INSERT, UPDATE, DELETE or SELECT from a table. This dovetails perfectly with the use of triggers because it allows you to think of security entirely in terms of table access, which is what it all boils down to in the end anyway. Using triggers plus table security gives the tightest possible integration of security and business logic, and puts your entire security framework on a simple and consistent basis.

Third Example: Composition

The Object Oriented Programmer is used to instantiating and using objects together in different patterns. This technique, known as "composition", is one of the basic ways for classes to interact with each other. I will not argue here that triggers are an exact analog to composition, because such a statement is not true and will invite meaningless debates over terms. What I will argue is that within a database the use of triggers to write to other tables accomplishes the same goal: the interaction of code from different but related contexts.

To illustrate this, our third example makes use of the cascading effect described above to link together the ORDER_LINES table, the ORDERS table, and the CUSTOMERS table. The idea is this. One of the rules on the ORDER_LINES table is that the extended_price must be calculated whenever price or qty change. But there is also a rule on the ORDERS table that it must always contain the total of the line items of the orders. Now let us imagine that the CUSTOMERS table must always contain the total of all open orders for the customer. A complete example for INSERT, UPDATE and DELETE would be far more than will fit here, but if we concentrate just on UPDATE operations we can see the general idea:

-- This is a modified form of example one above
CREATE FUNCTION order_lines_before_row_func RETURNS TRIGGER AS
$BODY$
   -- EXTEND The price
   SET new.extended_price = new.price * new.qty
   
   -- AGGREGATE the total to the sales order,
   -- notice that we are subtracting the old value and
   -- adding the new
   UPDATE ORDERS set lines_total 
         = COALESCE(lines_total,0) 
         + new.extended_price
         - old.extended_price;
$BODY$
SECURITY DEFINER LANGUAGE PLPGSQL;

-- The trigger above affected the ORDERS table, and now
-- we go the next step and affect the customers table
CREATE FUNCTION orders_before_row_func RETURNS TRIGGER AS
$BODY$
   -- AGGREGATE the total to the customer
   UPDATE CUSTOMERS set orders_total 
         = COALESCE(orders_total,0) 
         + new.lines_total
         - old.lines_total;
$BODY$
SECURITY DEFINER LANGUAGE PLPGSQL;

-- Postgres requires this 2nd step, but I'm not going into it here
CREATE TRIGGER order_lines_before_row_trigger .....
CREATE TRIGGER orders_before_row_trigger.....

I want to repeat here that I am not claiming this mechanism is an exact analog to composition, but I do want to stress that when you go with triggers a lot of code disappears out of your application, and you may find yourself wondering how all of this fits with the OOP mentality. All programming systems require the basic ability to both isolate code while also defining and controlling how these isolated fragments can interact. OOP makes use of inheritance and composition to relate code segments to each other, but triggers change the scene quite radically. The trigger encapsulates the behavior of the table, and because triggers can update other tables, the ability to have cascading or secondary actions is satisfied.

My final note on this idea is to repeat something I have stressed in these essays many times. The seasoned and experienced database programmer thinks of things in terms of his basic building block, the table. The trigger gives him the ability to attach code directly to this building block. The fact that triggers can update other tables gives him the necessary mechanism to have operations that span multiple tables.

Future Essays on Triggers

There is far more to cover on triggers, and I have glossed over many details in this first introduction. Future essays will demonstrate table design patterns that have their cleanest implementations with triggers, and we will also examine grave performance issues that must be considered on large complex systems. Before we get to that however, next week we will have an introduction to meta data and data dictionaries, which we have to cover first because they lead to more essays on triggers and code generation, and the data dictionary will play more and more into future essays on table design patterns.

Conclusion

Triggers are a very powerful mechanism for creating business logic that is guaranteed to execute, cannot be subverted, dovetails nicely with security. The trigger has the curious property of strongly encapsulating code and data, even though we do not usually use the term encapsulation to describe this. Triggers put great power in the hands of the programmer.

Related Essays

This blog has two tables of contents, the Topical Table of Contents and the list of Database Skills.

The normalization essays on this blog are:

17 comments:

oraclenerd said...

Ken,

Why use triggers at all? Why not just put the logic you would put in your trigger into the procedure/function which inserts the row?

chet

Tonguç said...

In my opinion triggers are like fast-food, delicious in short-term but harmful in long-term, here I share some examples to support my opinion;

http://tinyurl.com/54wct6

KenDowns said...

Tonguç: I cannot dispute the experience you list in your blog, but you don't have to give up the advantages of triggers, it comes down to how you respond to these realities.

I suspect that we differ in our approach in that I make exclusive use of a data dictionary (which will be introduced in next week's essay). The data dictionary defines the "automagic" features, which themselves follow very strictly the denormalization patterns I have listed on this blog. The approach is to generate the trigger code and the documentation simultaneously from the dictionary, so that the first two problems you list in your link do not exist for me. The third issue also does not exist for me, but that is larger than I can get into in a comment, I will note only that it also centers on using the data dictionary and a strong build program to generate all of the DML.

KenDowns said...

oraclenerd: Many reasons. The main reason cited in the article was to prevent subversion. If you put the logic into the application, then you can only write to the database safely through the app. In some cases this is ok, but in my world of business apps it is very much not ok. We have to know that the database can be accessed from any source and still be able to "defend itself" from accident and malice. There are also performance issues, but the subversion thing is the big deal, that's why I conceptually linked it to the OOP concept of encapsulation.

Tonguç said...

Ken you are right, the choice to use or not to use triggers mostly depend on the needs like anything else.

Additionally the performance view was recently discussed at the Oracle OTN forums, the use of SQL inside triggers may result parsing problem for heavy OLTP kind of applications, here is a small example;

http://tinyurl.com/4pt27l

To my experience on Oracle databases moving SQL from the triggers into PL/SQL packages, functions, procedures and call only PL/SQL from triggers helps.

mrclay said...

"If you put the logic into the application, then you can only write to the database safely through the app."

You forgot to mention only one instance of the DB writing code can be running at one time (basically impossible to enforce in, say, a PHP app).

KenDowns said...

Mrclay: I do not understand your comment.

Since db's are inherently multi-user I did not "forget" to mention anything about multi-user restrictions. Can you clarify?

mrclay said...

Ken: it was too early! I was trying to say that, with no integrity enforcement in the DB, even if you only use a single app to do writes, if that app is a PHP web app w/ simultaneous users causing writes, this could be "unsafe".

Am I off-base here?

KenDowns said...

Mrclay: I do not think you are off base. You can make an application safe for multiple-access with such strategies as row locking, but IMHO this is quite inferior to encapsulating the biz logic with the tables by using triggers.

ericdes said...

I don't mind putting the logic at the database level -- but when an action is forbidden what kind of error message will display for the end user? When I validate an entry (e.g. "#", ";", "'" are not authorized in a username) I take the extra step of returning the exact error message, such as 'You cannot put the character # in the username' and not only 'Invalid character(s) in the username', which is relatively easy to accomplish when coding at the application level. What about the messages returned by the rdbms?

KenDowns said...

@ericdes: For error messages I provide a semi-colon delimited list of errors. Each error contains a comma-delimited list of 3 pieces of info, a #, the affected column, and a string. The use of the error # allows app programmers to have their own table of text messages to replace mine, and it also hooks the system for future i18n efforts.

ericdes said...

I suppose you have the trigger raised an exception with a string that holds the semi-comma delimited list of errors? Does it translate as a meta data in your project Andromeda or is it something you have to hand-code within the trigger?

KenDowns said...

@eric, yes the trigger raises the error. At this time I have no meta-data in the framework, the text of the errors is hard-coded.

The use of the error # and the column id is meant to allow an application programmer to hook in his own errors if need-be, as one of my customers has done.

Hanh said...

Ken, than you for this blog. I enjoy it very much. Please allow me a few questions related to this discussion, which perhaps you can expound on in a later blog.

In a complex system, would not extensive use of triggers and their cascading effects create problems extremely difficult to diagnose in the real world? For example: Trigger A modifies Table B, trigger B modifies Table A >> loop?

Can subversion be implemented with appropriate permission control of the stored procs/functions? For example: If function playlist_before_row_func() is owned by user X and is executable by user Y, would that prevent subversion by user Y of the code controlled by X (and the tables over which that code operates) while allowing Y more control over the code execution?

The problem with extensive use of triggers in large systems is the issue of tracing and diagnostics related to the complexity of the trigger interactions. The big picture must be understood and considered at all times, which in a sense negates the benefits of encapsulation.

When data is changed, tracking that change back to the source action can be (too) complex especially if more than 1 triggers is involved on the same table, and where cascading of trigger effects are in place. Modifying a system with triggers can be much more daunting than one where all functions/procs are executed in a more "controlled" (per the developer/app) manner. For myself, that is the main reason I have stayed shy of extensive use of triggers outside simply generating unique primary keys.

KenDowns said...

@hanh: The concerns you mention are all very real if you code the triggers manually. When the triggers are coded by programmers, you simply move all of your complexity from app code to trigger code.

But there is a better way.

First, recognize that all application code executes operations that fall into patterns. Or, put another way, a person who understands the patterns will always be able to see them, and a person who does not recognize them will swear they are impossible.

Because the application's actions fall into patterns, an entire application can be described in meta-data, most of it comes down to formulas and rules about columns.

Once you have a body of meta-data, you can generate the triggers. This is what I do. I spend my time writing descriptions of databases that include formulas, rules for writing to history tables, security and so forth, and my builder program writes the triggers for me.

Alan Shields said...

Ken, your "Denormalization Patterns" link has stray characters at the end. Thanks for the great article!

(by the way, have you considered running a spider to check for dead links on your page? Eliminates these problems)

David Miller said...

Nowadays, smart businesses and industries are opting for this long run encapsulation on those products that serves the most essential or influential components of their businesses. These actually are essential objects that promote sales and purchases and offers a handful of benefits for the company.