One of my 2015 new year's resolutions, was to finish the story I started on this blog. The story being a talk that I had delivered a couple of times 3-4 years ago on various Oracle/usergroup events. The talk is about why I think triggers should *not* be considered harmful. There is one specific use-case for triggers -- implementing validation code for what's called a multi-row constraint -- that I will treat in-depth on this (more...)

In a previous post we've introduced a classification scheme for constraints:

attribute constraints

tuple constraints

table constraints

database constraints

dynamic constraints

And talked a bit about how we could implement the first two classes here. In today's post we will make a start talking about how we can implement table constraints using triggers. But before we do that we will offer some thoughts on how the ideal world with regards to this subject would look like.

Long ago, in a galaxy far away, an ANSI/ISO SQL standardization committee came up with the ground breaking concept of a SQL ASSERTION. SQL assertions would (more...)

In our previous post we have introduced a classification scheme for data integrity constraints. In todays post we will present thoughts & guidelines around how to implement the first two classes: attribute and tuple constraints.

The examples given in the previous post were:

Attribute constraints

Salary must be a positive integer between 750 and 14000.

Job must be one of the following list: MANAGER, CLERK, SALESMAN, etc.

Tuple constraints

A manager cannot have a salary less than 5000.

An employee working in department 10, cannot be a salesman.

We could implement these constraints using a trigger approach. Let's go (more...)

Before we start investigating the complexities involved in implementing data integrity constraints using database triggers, we will first introduce a classification schema for data integrity constraints. Agreeing upon a classification scheme for constraints, helps us a lot in implementing them: for we can then approach the problem area on a class-by-class basis.

A classification scheme needs to have a few properties:

The constraint classes should be mutually exclusive: any given constraint should clearly fall into one, and only one, of the classes;

The scheme should cover all types of constraints: i.e. it cannot be that we can (more...)

Just attended this session here at Oracle Openworld:
Flexible Design and Modeling: Planning for Constant Change
Gwen Shapira - Senior Consultant, Pythian
Robyn Sands - Principal Member of Technical Staff, Oracle
Which was expressing the same message as the Helsinki Declaration does:
Create API's in the database layer and do not let outside software layers access tables directly.
It

In our previous post we talked about three of the four use-cases we introduced. Triggers can:

Assign/modify (row) column values.

Execute insert/update/delete statements.

Execute non-transactional stuff.

We've also discussed why indeed using triggers for above use-cases, should be considered harmful. Their automagic side-effects are often not expected by other programmers / users of the database application.

In this post we continue with use-case four. Triggers can:

Execute select statements.

This use-case, where we have the need to execute queries inside triggers, is a very common use-case, and it is one that always (re)surfaces, in almost every project. And there's a (more...)

Sofar we've explored playing around with a few triggers to implement a business rule. In this post I'd like to step back a bit and take a birds-eye view at the common use-cases that I see brought up for introducing triggers into the app-development picture.

The first use-case is: we use triggers to assign or modify column values of mutating rows (that is, rows currently being inserted, updated, or deleted). Here's a few typical examples of this use case that, I'm sure, must look familiar to you too.

For this use-case we always use a row-level trigger (for each row), (more...)

We wanted to implement the following business rule: "Cannot have a manager without a clerk in the same department." At the end of this post, we tried implementing that using a straight-forward row-trigger, which introduced us to the mutating table error. And we've explained thereafter (here and here) that this error is there for a very good reason.

Did you ever wonder why it says 'trigger/function' in the error message of ORA-04091?

ORA-04091: table ... is mutating, trigger/function may not see it

We know (and understand) by now that a row trigger cannot read a mutating table, but what's the /function all about in above message text? Well there is a completely different scenario that has nothing to do with triggers, where you can run into this error. I thought to spend a short post on that first, so that you really fully understand that ORA-04091 is your friend. And again in this scenario it prevents you from (more...)

In the previous post, we demonstrated running into the mutating table error. The example involved the well-known EMP table and a business rule that we are trying to implement: all departments with a Manager should also employ a Clerk. We started our implementation for that rule by writing a straight-forward 'after-row-insert' trigger on the EMP table.

The row trigger detects whenever a Manager is inserted (remember, we can only detect this with a row trigger, not with a statement trigger);

It then calls a stored procedure to which it supplies the department number (deptno-column value) of the inserted Manager;

There are a few remarks to be made on DML event triggers. Just to be sure we are all at the same level on the playing field. The first one is that since these triggers fire as part of the execution of a DML statement, the trigger code is not allowed to execute any DDL (which causes an implicit commit) or (explicit) transactional control statements, such as commit, rollback, savepoint or rollback to savepoint. This restriction is true for both statement level as well as row level DML event triggers.

This post will deal with the first bullet: the scope, what triggers are we talking about? And what triggers are we not talking about. Then there will probably be a few posts on 'properties' of the triggers, most notably I will spend some time on explaining the infamous mutating table error. Next we move on to a high level classification of use-cases of triggers. And talk a bit about why some of these might be considered harmful. Finally we will explain, in detail, the one use case where triggers are the perfect means to (more...)

I know I should finish posting here on TheHelsinkiDeclaration... But in the meantime I decided to start a new blog dedicated on database triggers. It will be like TheHelsinkiDeclaration a documentation of a presentation I have been giving recently entitled "Triggers considered harmful", considered harmful.

So I finally was able to find some time to start this blog. I mentioned before that I might do this in the asktom discussion here. This blog will be about database triggers. Oracle database triggers that is. I'm a strong advocate of database triggers. Now hold your horses... I have been using database triggers ever since Oracle7 in the early nineties brought them to us. But I've been using them for a very specific reason, and have been presenting about this ever since. I will blog about this in a very similar way as I blogged about TheHelsinkiDeclaration, (more...)