Roland Bouman wrote:
Hi!
On Fri, Aug 7, 2009 at 12:17 AM, Jim Starkey<[email protected]> wrote:
Triggers, ladies and gentlemen, are wonderful. The SQL standard version
stinks and isn't worth implementing. Decent triggers are sublime. Please
don't write them off because you don't understand them.
(changed subject to not hijack the original thread.)
Sorry if this is really obvious, but I'm curious as to how the
standard's version of triggers stinks, and of course, also curious
what "decent triggers" are.
thanks in advance and kind regards,
I think the requirements for triggers include:
1. Procedural structure to support complex logic
2. Access to before and after records
3. Before and after triggers for insert, update, delete, and commit
4. Multiple triggers per table/operation
5. An order mechanism for triggers on a table/operation
6. An (optional) mechanism to disable triggers by class on a
per-connection basis.
7. No restrictions on colateral updates
8. Triggers cascade
9. Triggers must be able to throw exceptions that abort the operation
I would add some more things that are advisable but not requirements:
1. A uniform invocation mechanism -- let the trigger determine its type
2. Table independent triggers
3. A cheap metadata access mechanism.
These allow a single trigger to be defined to do things like application
specific replication in an easy, straightforward, and robust.
Sybase and Interbase both had triggers in their first version (I
released them first). Interbase triggers cascaded. Sybase triggers did
not, which severely restricted their usefulness. Interbase triggers
were written in GDML, the evolution of the Datacomputer Datalanguage,
Datatrieve, and DEC DML.
Interbase triggers were an instant hit. A customer who had implemented
her application before triggers were implemented actually had tears
running down her face when I explained them. The first problem --
completely unanticipated -- was the triggers were trying to do too many
things and got horribly complicated. This lead to multiple triggers per
operation, which completely solved the problem.
A problem that took a company or two to resolve was de-activating
triggers for specific mass operations. Interbase allowed a trigger to
be globally de-activated / re-activated, which did the trick, but had
other undesirable characteristics. In Netfrastructure, I added an
optional "class" clause to the trigger definition to enable named
trigger classes to be disabled on a per-connection basis.
Triggers are to databases what methods are to objects. Done well, they
allow data semantics to be encapsulated in the database and
automatically -- and reliably -- applied.
Things triggers are really good at include:
* Complex data validation
* Unique key generation
* Auditing
* Replication
* Logging
<rant heat="low">
One of the sad aspects of MySQL is how little it was used internally,
particularly in engineering. Yes, the support groups used it. But if
engineers had to develop their own support applications, I think MySQL
would look quite different. At Interbase, we used Interbase as a code
managers, an email system, a bug tracking system, a test system, a test
failure analysis tool, etc. When engineers actually have to use the
product they develop, things like the need for triggers, data
validation, field initialization, and search become apparent. People
who design things for other people don't get the benefit of a rapid
feedback loop.
</rant>
--
Jim Starkey
Sent from Shearwater, off
the coast of New England
_______________________________________________
Mailing list: https://launchpad.net/~drizzle-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~drizzle-discuss
More help : https://help.launchpad.net/ListHelp
