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
   -- 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;

-- 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 
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.

   -- 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;

-- 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
 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:

 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
   -- 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;

-- The trigger above affected the ORDERS table, and now
-- we go the next step and affect the customers table
   -- AGGREGATE the total to the customer
   UPDATE CUSTOMERS set orders_total 
         = COALESCE(orders_total,0) 
         + new.lines_total
         - old.lines_total;

-- 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.


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:


oraclenerd said...


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?


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;

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;

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.

Anonymous 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?

Anonymous 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.

Anonymous 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.

Anonymous 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)

Unknown 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.

Enthusiasm Quotes said...

Very Nice And Interesting Post
Inspirational Quotes - Gym Quotes
Best Quotes - Success Quotes
Positive Life Quotes - Image Bank
Future Quotes - حكمة اليوم
اقوال وحكم - حكم قصيرة

Kajal Agarwal said...

Norton Security has been giving the best on-line security answers for ensuring the clients' contraptions other than as information against the web threats. to get more about norton click download norton with product key now.

Kajal Agarwal said...

mcafee enter product key code - Shielding the computing devices against dangerous online threats, malware, phishing scams, Trojan horses and other infections.

Kajal Agarwal said...

Microsoft Office install has required the removal of the previously installed version of your Office product on the device or system. activate office and other subscription offers the various features, which you do not get when you do not purchase the Office product. The office can be used free, as Microsoft provides the trial versions of every tool.

Kajal Agarwal said...

Fast & Easy PC Virus Protection. Download avg internet security Now. Stops Viruses, Spyware, Ransomware and Other Malware. Easy Installation. Install in moments. Light & easy installation. Family protection. Protects forever. Stops viruses. Unsafe web link warnings.

Kajal Agarwal said...

Antivirus is the need of PCs that makes them infection free and we are going to give you full help to get the best antivirus introduced in your PCs and PCs. Download norton antivirus bundle with us - norton antivirus for more details visit today.

Kajal Agarwal said...

Real-time problems and outages for Office 365. Is the service down? Can't login or connect? Here you see what is going on. for more details visit office activation today.

Kajal Agarwal said...

To activate Hulu Plus on your device, either use the on-screen keyboard to enter your Hulu log in information or go to hulu tv activate code and enter the device activation code. Either of these methods will allow you to use Hulu on any Hulu-supported device.

Kajal Agarwal said...

We provide help & support for Roku setup & account login, when Roku not connecting & Remote not working. visit Enter Link Code to Activate Roku today.

Kajal Agarwal said... will help you to deploy the office setup successfully on your device. Here are easy steps to download, install and activate office setup. Visit download office 2016 now.

Kajal Agarwal said...

It is extremely important to protect your PCs, Mac, computers as well as mobile devices and tablets with avg retail registration .

Kajal Agarwal said...

mcafee antivirus is broadly utilized antivirus identifies and kill PC infection, the mail worms,the trojan programs,and additionally enables your framework to free of infection and other malware is a significant every day challenge. for more subtleties visit: enter mcafee activation code today.

Kajal Agarwal said...

Antivirus is the need of computers that makes them virus free and we are going to give you full support to get the best antivirus installed in your computers and laptops. Download norton antivirus package with us - norton security setup with product key today.

Kajal Agarwal said...

Download and introduce norton com setup with product key Sign In to Norton. On the off chance that you don't have a Norton account, click Create an Account and complete the sign up procedure. In the Get Started window, click Enter a New Product Key. To take a crack at Automatic Renewal Service for your Norton membership.

Kajal Agarwal said...

Download, Install & Activate office my account for home & Business purpose and get full technical help for office setup installation. Visit : my office account 365 for more details.

Kajal Agarwal said...

Download office setup with our help team without getting any work done by yourself. For more visit activate office 2016 with a product key now.

Kajal Agarwal said...

Download webroot install with key code Antiviurs If you own a device or system and you are connecting it to internet or another device then you must have antivirus software. Virus or any risky threat like Malware, Trojan, Spyware, Rootkit or online hacking or attack can steal your data and damage your system.

saloni said...

Visit in order to know everything about the hassle-free deployment process of Microsoft Office Setup 2019 and it's applications.

saloni said...

Microsoft Office setup has a wide range of products with different features at Install and activate it using the MS Office Setup activation key code.

saloni said...

Norton Setup – offers world-class security to your PC, Mac, Android, or iOS devices. Norton antivirus provides protection against malicious online threats while offering smooth system operation as well.

saloni said...

Go to the web browser and visit Then type the 25-digit number that you have purchased. Now download the McAfee setup and install and wait for a few minutes to restart and update it. Create a Mcafee antivirus account.

saloni said...

Mcafee Antivirus has recognized to be an effective way of keeping up your devices and your data safe from getting infected by any hackers and virus attackers. will help the user to get easy support.

saloni said...

Get started with office setup at It provide ease of working and systematic arrangement of your worksheet.

saloni said...

Webroot Antivirus is the best security solution which gives total protection and 100% safety from being damaged by harmful viruses. Get your webroot today at All the devices like PC's, Laptops, Mac, Ios, and Android Phones in which we use internet connection are safe with the use of Webroot Antivirus.

saloni said...

Norton is an antivirus and security software for Windows, Mac, iPhone, and Android. It has many different features for protecting the device from various web threats and the main features in the Norton software are anti-spyware, web browser protection, antivirus, and firewall security. will help the user to enjoy all these amazing features.

saloni said...

Norton is an antivirus and security software for Windows, Mac, iPhone, and Android. Visit as it has many different features for protecting the device from various web threats and the main features in the Norton software are anti-spyware, web browser protection, antivirus, and firewall security.

saloni said...

Hulu is one of the best streaming services that ease access to content on different types of devices. You can stream across computers, mobile devices and gaming consoles at However,before enjoying this service you will need to subscribe to the service and activate your Hulu account.

saloni said...

To install Norton that you purchased from or from a retail store, you must create a Norton account or add the purchased product to the existing account.

saloni said...

Webroot is one of the best online security program if you are looking for an antivirus for your computer and mobile. You can get this program at and create your webroot account today.

saloni said...

Activate your Webroot Account by entering your Webroot Activation Code. Facing issues…visit to get easy and quick steps to activate and install webroot with key code.

Crypto Exchange And Wallet Support said...

Are you unable to send or receive an important attachment to your client due to the forgetting of your Mycelium password? If so, then you need to quickly take the support by dialing a Mycelium customer support number. The phone number is an apt choice for Mycelium users to get the best tech support for all relating concerns. The phone number will help you to connect with the dedicated Mycelium account professionals so that you can get the on-time Mycelium support. Feel free to dial the number at any because the number is obtainable 24/7 for the customers.

The Marketer said...

I enjoyed your blog Thanks for sharing such an informative post. We are also providing the best services click on below links to visit our website.

digital marketing company in nagercoil
digital marketing services in nagercoil
digital marketing agency in nagercoil
best marketing services in nagercoil
SEO company in nagercoil
SEO services in nagercoil
social media marketing in nagercoil
social media company in nagercoil
PPC services in nagercoil
digital marketing company in velachery
digital marketing company in velachery
digital marketing services in velachery
digital marketing agency in velachery
SEO company in velachery
SEO services in velachery
social media marketing in velachery
social media company in velachery
PPC services in velachery
online advertisement services in velachery
online advertisement services in nagercoil

web design company in nagercoil
web development company in nagercoil
website design company in nagercoil
website development company in nagercoil
web designing company in nagercoil
website designing company in nagercoil
best web design company in nagercoil
web design company in velachery
web development company in velachery
website design company in velachery
website development company in velachery
web designing company in velachery
website designing company in velachery
best web design company in velachery

Thanks for Sharing - ( Groarz branding solutions )