views:

281

answers:

1

For another question, I'm running into a misconception that seems to arise here at SO occasionally. Some questioners seem to think that Triggers are to Databases as Events are to OOP.

Does anyone have a good analogy to explain why this is a flawed comparison, and the consequences of misapplying it?


EDIT:

Bill K. has hit it correctly, but maybe doesn't see the importance of the critical differeence between the event and the callback function that strikes me, anyway. Triggers actually cause code to execute every time the event occurs; callbacks only occur whenever one has been registered for an event (which is not true for the vast majority of events); and even then, in most cases the callback's first action is to deregister itself (or at least the callback contains a qualifcation exit so it only executes once.)

If you write a trigger, it will unfailingly execute every time the event occurs, because there's no way to register or deregister to code segment.

Triggers are a way to interpose repeating logic synchronously into the thread of execution (i.e. synchronicity). Events are a means to defer logic until later (i.e. implement asynchronicity).

There are exceptions and mitigations in both cases, but the basic patterns of triggers and callbacks are mostly opposite in intention and implementation. Often the distinction doesn't seem to have fully sunk in. (IMHO, YMMV). :D

+5  A: 

They're not the same thing, but they're not unrelated.

In both cases, the mechanism can be described approximately as follows:

  • Some block of code declares "interest" for changes in state.
  • Your application affects some change.
  • The system runs the block of code in response to the change.

Perhaps a database trigger is more like a callback function that has registered interest in a specific event.

Here's an analogy: the event is a rubber ball that you throw. The trigger is a dog that chases after a thrown ball.

If there's some other difference that you have in mind that makes it "dangerous" (note: OP has edited this choice of word out of the question) to compare triggers and events, you can describe what you mean.


Triggers are a way to interpose repeating logic synchronously into the thread of execution (i.e. synchronicity). Events are a means to defer logic until later (i.e. implement asynchronicity).

Okay, I see what you mean more clearly. But I think it's in some ways subject to the implementation. I wouldn't assume an event handler has to deregister itself; it depends on the system you're using. A UNIX signal handler, for example, has to prevent itself from catching a new signal while it's already handling one. But a Java servlet inside a Tomcat container should be thread-safe because it may be called concurrently by multiple threads. They're both event handlers, of different kinds.

Event handlers may be synchronous or asynchronous. Can a handler in a publish/subscribe system read messages that were posted recently, but prior to the handler registering its interest? Or only messages posted concurrently?


There's another important reason to treat triggers as different from event handlers: I frequently recommend against doing anything in a trigger that affects state outside the database.

For example, sending an email, writing to a file, posting to a web service, or forking a process is inappropriate inside a trigger. If for no other reason than the transaction that spawned the trigger may be rolled back, but you can't roll back those external effects. You may not even be using explicit transactions, but say you send an email in a BEFORE trigger, but the operation fails because of a NOT NULL constraint or something.

Instead, all such work should be done by code in one's application, after one has confirmed that the SQL operation was successful and the transaction committed.

It's too bad that people keep trying to do inappropriate work inside a trigger. There are senior developers at MySQL who promote UDFs to read and write data in memcached. Wow -- I just noticed these have made it into the MySQL 6.0 product!! Shocking!

So here's another attempt at an analogy, comparing triggers and events to the process of a criminal trial:

  • A BEFORE trigger is an allegation.
  • An AFTER trigger is an indictment.
  • COMMIT is a conviction after a guilty verdict.
  • ROLLBACK is an acquittal after an innocent verdict.

You only want to put the perpetrator in prison after they are convicted.

  • Whereas an EVENT is the crime itself.
Bill Karwin
I believe 'he' is referring to certain cavelier attitudes as to what fundtionality makes sense in a trigger
Mitch Wheat
so a Deadlock is when 2 dogs get hold of the same ball? ;)
Mitch Wheat
One specific example was calling a web service with CLR from within a trigger. I call that dangerous.
le dorfier
Looks like MySQL has been caught in the chronic Sun disease - Microsoft Envy. Trying to match feature for feature.
le dorfier