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 "http://database-programmer.blogspot.com/2008/04/denormalization-patterns.html"
>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 "http://www.liphp.org/"
>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 "http://database-programmer.blogspot.com/2008/05/introducing-database-security.html"
>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
Update Contact :
No Wa/Telepon (puat) : 085267792168
No Wa/Telepon (fajar) : 085369237896
Email : Fajarudinsidik@gmail.com
No Wa/Telepon (puat) : 085267792168
No Wa/Telepon (fajar) : 085369237896
Email: Fajarudinsidik@gmail.com
atau Kirimkan Private messanger melalui email dengan klik tombol order dibawah ini :
