Can databases talk back?

That depends on what scripting systems the database uses for triggers.

PostgreSQL supports Tcl, Perl, and Python all of which should let you call an external process. And if all else fails Writing Trigger Functions in C. I don't have much experience with scripting triggers in other databases.

Quote:

Original post by d000hg And is it actually useful?

You want to be careful though. A database is essentially an advanced data storage device. Although it's perfectly acceptable to have database triggers that act on other data in order to preserve data integrity, I would be very reluctant to have a database spawn or control processes outside its natural domain. This is what the business logic in an application layer should do. If every module can modify the state of every other module it can quickly become an absolute mess.

Stored procedures are pieces of code that reside on database server. What language they use is mostly irrelevant, and various database engines provide different options. Oracle server contains a fully-fledged JVM which is capable of running Java code. But it runs within the server.

Quote:

So, is it available? And is it actually useful?

Yes, by the very nature of how databases work (passive, event driven), they do have ability to trigger events. These are provided by most database engines, and give you a way to react to queries.

The problem comes from database access being context independent. A query doesn't know what is calling it. It could be client, it could be some automated tool, it could be database itself, it could anything, and that something might not have a context.

All enterprise-grade frameworks provide way to respond to database events in one way or another. The topic however is very complex and involves many issues with regard to atomicity.

For example, consider a database that fires an event when money is deposited into a certain account. When that event is fired, a bomb goes off. But what happens if the event is fired, but then, the transaction is rolled back, signalling that event should not take place?

As such, external events bring about many conceptual problems which are impossible to solve, so these types of notification tend to be part of application framework, and not the database itself.

For all such problems, there exist ways to do them without callbacks.

postgresql is one who can do this natively (without pl/perl or other plugins), it's called 'listen/notify' in postgres:
documentation here

they're solution for 'bomb goes off when account is updated' is : send notifications ONLY if transaction commits.

It also has some overhead and lots of drawbacks, I'm using postgres since 2004 and never had to rely on listen/notify for anything serious in my databases.

Also, firebird has somethings similar (if I remember correctly)..

Quote:

Original post by cobru postgresql is one who can do this natively (without pl/perl or other plugins), it's called 'listen/notify' in postgres: documentation here they're solution for 'bomb goes off when account is updated' is : send notifications ONLY if transaction commits. It also has some overhead and lots of drawbacks, I'm using postgres since 2004 and never had to rely on listen/notify for anything serious in my databases. Also, firebird has somethings similar (if I remember correctly)..

I suppose that this is a limited approach: what if there are operations that have to be performed on data modified or created by that function? in the example of the bomb, what if the first instruction after the bomb launch counts the victims?