MySQL 5.0 Trigger Reference Tutorial 1th/4 page _mysql

Source: Internet
Author: User
Tags exception handling mysql client
Conventions and styles conventions and programming styles
Every time I want to demonstrate the actual code, I adjust the code that appears on the MySQL client's screen, changing the font to courier so that they look different from normal text (which lets you distinguish between the program code and the body). Here's an example:

Mysql> DROP FUNCTION F;
Query OK, 0 rows Affected (0.00 sec)
If the instance is larger, you need to annotate some lines and paragraphs, and I will use the "<--" symbol on the right side of the page to emphasize. For example:

Mysql> CREATE PROCEDURE P ()
-> BEGIN
->/* This procedure does nothing * * * <--
-> end;//
Query OK, 0 rows Affected (0.00 sec)
Sometimes I will remove the "mysql>" and "->" systems in the example, and you can copy the code directly to the MySQL client (if you are not reading the electronic version, you can download the relevant script on the MySQL.com website)

So the examples have been tested in the SuSE 9.2 Linux, Mysql 5.0.3 public edition. As you read this book, MySQL already has a higher version and can support more OS, including Windows,sparc,hp-ux. So the example here will be able to run normally on your computer. However, if the operation still fails, you can consult the senior MySQL users you know, so that you can get better support and help.

Why triggers Why do you use triggers

We included support for triggers in MySQL 5.0 for the following reasons:

MySQL's earlier version of the user needs a trigger request for a long time.

We have promised to support the attributes of all ANSI standards.

You can use it to check or prevent bad data from entering the database.

You can change or cancel the INSERT, UPDATE, and DELETE statements.

You can monitor the movement of data changes in a session.


Here I assume that everyone has read the first episode of the MySQL new feature series, "MySQL stored procedures," so everyone should know MySQL to this stored procedure and function, which is very important knowledge, because in the trigger you can use the statement used in the function. In particular, give an example:

Compound statements (begin/end) are legal.

Flow control (Flow-of-control) statements (IF, case, while, loop, while, REPEAT, leave,iterate) are also legal.

Variable declarations (DECLARE) and Assignment (SET) are legal.

Allow conditional declarations.

Exception handling declarations are also allowed.

But here you have to remember that the function has a restricted condition: You cannot access the table in the function. Therefore, it is illegal to use the following statement in 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
There are exactly the same restrictions in triggers.

Triggers are relatively new, So there will be (bugs) defects. So I'm here to warn you, as I said in the stored procedure book. Do not use this trigger in a database that contains important data, use it on some database for testing purposes if necessary, and confirm that the database is default when you create a trigger on a table.
Current 1/4 page 1234 Next read the full text
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.