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:

• Revisiting Normalization and Denormalization
• Pay Me Now Or Pay Me Later
• The Argument for Normalization
• First Normal Form
• Second Normal Form
• Third Normal Form and Calculated Values
• The Argument for Denormalization
• Denormalization Patterns
• Keeping Denormalized Values Correct
• Triggers, Encapsulation and Composition (this essay)
• The Data Dictionary and Calculations, Part 1
• The Data Dictionary and Calculations, Part 2