MySQL 5.0 New Features: Triggers

By Peter Gulutzan

Introduction

This book is for long-time MySQL users who want to know "what's new" in version 5. The short answer is "stored procedures, triggers, views, information_schema". The long answer is the "MySQL 5.0 New Features" series, and this book is the second in the series.

What I'm hoping to do is make this look like a hands-on session where you, as if you're working it out yourself on your keyboard, can walk through sample problems.

To do this, I'll go through each little item, building up slowly. By the end, I'll be showing larger routines that do something useful, something that you might have thought was tough.

Conventions and Styles

Whenever I want to show actual code, such as something that comes directly from the screen of my mysql client program, I switch to a Courier font, which looks different from the regular text font. For example:

mysql> DROP FUNCTION f;
Query OK, 0 rows affected (0.00 sec)

When the example is large and I want to draw attention to a particular line or phrase, I highlight it with a double underline and a small arrow on the right of the page. For example:

mysql> CREATE PROCEDURE p ()
    -> BEGIN
    ->   /* This procedure does nothing */
    -> END;//
Query OK, 0 rows affected (0.00 sec)

Sometimes I will leave out the "mysql>" and "->" prompts so that you can cut the examples and paste them into your copy of the mysql client program. (If you aren't reading the text of this book in a machine-readable form, try looking for the script on the mysql.com web site.)

I tested all the examples with the publicly-available alpha version of MySQL 5.0.3 on Linux, SUSE 9.2. By the time you read this, the version number will be higher and the available operating systems will include Windows, Sparc, and HP-UX. So I'm confident that you'll be able to run every example on your computer. But if not, well, as an experienced MySQL user you know that help and support is always available.

Why Triggers

We are including support for triggers in MySQL 5.0 for these reasons:

• Users of earlier MySQL versions kept on telling us they wanted triggers.
• We have a commitment to support all ANSI-standard features.
• You can use them to check for, and prevent, bad data entering the database.
• You can change or negate the INSERT, UPDATE, and DELETE statements.
• You can monitor data-change activity throughout a session.

I assume that you have read the first book in the "MySQL New Features" series already. In that book, "MySQL Stored Procedures", you (I hope) saw how MySQL supports stored procedures and functions. That's important knowledge, because you can use the same statements in triggers as you can use in functions. Specifically:

• Compound statements (BEGIN / END) are legal.
• Flow-of-control statements (IF, CASE, WHILE, LOOP, WHILE, REPEAT, LEAVE, ITERATE) are legal.
• Variable declaration (DECLARE) and assignment (SET) are legal.
• Condition declarations are legal.
• Handler declarations are legal.

But remember that functions are subject to severe limitations: you cannot access tables from within a function. So these statements are illegal inside a function:

  ALTER 'CACHE INDEX' CALL COMMIT CREATE DELETE
  DROP 'FLUSH PRIVILEGES' GRANT INSERT KILL
  LOCK OPTIMIZE REPAIR REPLACE REVOKE
  ROLLBACK SAVEPOINT 'SELECT FROM table'
  'SET system variable' 'SET TRANSACTION'
  SHOW 'START TRANSACTION' TRUNCATE UPDATE

Precisely the same limitation applies for triggers.

Read On

The whole article is long, so we thought it better to make it a PDF. To download, click here (no registration required!) You could even print out the PDF so you can peruse at your leisure.