Home > Article > Database > MySQL 5.0 New Features Tutorial Triggers: Lecture 1

MySQL 5.0 New Features Tutorial Triggers: Lecture 1

黄舟Original: 2016-12-19 16:39:55928browse

Conventions and StylesConventions and Programming Styles

　Every time I want to demonstrate actual code, I will adjust the code that appears on the mysql client screen and change the font to Courier so that they look different from ordinary text ( Let everyone distinguish between program code and text). Here is an example:

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

　If the instance is relatively large, you need to add comments between certain lines and paragraphs, and I will use The "<--" symbol is placed on the right side of the page for emphasis. 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 "->" system displays in the examples. You can copy the code directly into the mysql client program (if what you are reading now is not an electronic version , you can download the relevant scripts from the mysql.com website)

All the examples have been tested on Suse 9.2 linux and Mysql 5.0.3 public version. By the time you read this book, Mysql already has a higher version and can support more OS, including Windows, Sparc, and HP-UX. So the examples here will run normally on your computer. But if the operation still fails, you can consult an experienced Mysql user you know, so that you can get better support and help.

Why Triggers Why use triggers

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

Users of earlier versions of MySQL have long had a need for triggers.
　We promised to support all ANSI standard features.
　You can use it to check or prevent bad data from entering the database.
　You can change or cancel INSERT, UPDATE and DELETE statements.
　You can monitor data changes in a session.

Here I assume that everyone has read the first episode of the "MySQL New Features" series - "MySQL Stored Procedures", then everyone should know the MySQL stored procedures and functions. That is very important knowledge, because when triggering In the converter you can use the same statements that you use in functions. Special example:

Compound statements (BEGIN / END) are legal.

Flow-of-control statements (IF, CASE, WHILE, LOOP, WHILE, REPEAT, LEAVE, ITERATE) are also legal. .

　Variable declaration (DECLARE) and assignment (SET) are legal.

　Conditional declarations are allowed.

　Exception handling declarations are also allowed.

　But remember here that functions have restrictions: they cannot be in functions Access the table. Therefore it is illegal to use the following statements 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 TR ANSACTION'

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 am warning you here, just like I am in As stated in the stored procedure book. Do not use this trigger on a database that contains important data. If necessary, use it on some databases for testing purposes. Also make sure that these databases are the default when you create triggers on tables. .

Syntax Syntax

　　1. Syntax: Name Syntax: Naming rules

CREATE TRIGGER <--
{ BEFORE | AFTER }
{ INSERT | UPDATE | DELETE }
ON

FOR EACH ROW

　 The trigger must have a name, up to 64 characters, and may be followed by a delimiter. It is basically similar to the naming method of other objects in MySQL.

I have a habit here: use the name of the table + '_' + the abbreviation of the trigger type. Therefore, if it is table t26, the trigger is before the event UPDATE (refer to points (2) and (3) below) (BEFORE), then its name is t26_bu.

　　2. Syntax: Time Syntax: Trigger time

CREATE TRIGGER
{ BEFORE | AFTER } <--
{ INSERT | UPDATE | DELETE }
ON

FOR EACH ROW

　 The trigger has an execution time setting: it can be set before or after the event occurs.

　　3. Syntax: Event syntax: Event

CREATE TRIGGER
{ BEFORE | AFTER }
{ INSERT | UPDATE | DELETE } <--
ON

FOR EACH ROW

　 You can also set the triggered events: they can be triggered during the execution of insert, update or delete.

　4. Syntax: Table Syntax: Table

CREATE TRIGGER
{ BEFORE | AFTER }
{ INSERT | UPDATE | DELETE }
ON

<--
FOR EACH ROW

　 Triggers belong to a certain table: when an insert, update, or delete operation is performed on this table, the trigger is activated.
We cannot arrange two triggers for the same event in the same table.

　　5. Syntax: Granularity Syntax: ( : (step size) trigger interval

CREATE TRIGGER
{ BEFORE | AFTER }
{ INSERT | UPDATE | DELETE }
ON

FOR EACH ROW <--

　 Trigger execution interval: The FOR EACH ROW clause notifies the trigger to perform an action every other row, rather than once for the entire table

　　6. Syntax: Statement Syntax: Statement

CREATE TRIGGER
{ BEFORE | AFTER }
{ INSERT | UPDATE | DELETE }
ON

FOR EACH ROW
< Triggered SQL statement> <--

　The trigger contains the SQL statement to be triggered: the statement here can be any legal statement, including compound statements, but the statements here are subject to the same restrictions as functions

Privileges. Permissions

You must have considerable permissions to create a trigger (CREATE TRIGGER). If you are already a Root user, that is enough. This is different from the SQL standard, and I hope it can be changed to the standard as soon as possible.

　So in the next version of MySQL, you are entirely likely to see a new permission called CREATE TRIGGER. Then grant it in this way:

GRANT CREATE TRIGGER ON

TO ;

　Permissions can also be revoked like this:

REVOKE CREATE TRIGGER ON

FROM ;

Referring to OLD and NEW columns About old and new Identification of the created column

In the SQL statement of the trigger, you can associate any column in the table, but you cannot just use the name of the column to identify it, which will confuse the system because there may be a new name of the column. (This may be what you want to modify, your action may be to modify the column name), and the old name of the column exists, so you must use this syntax to identify it: "NEW . column_name" or "OLD . column_name". This technically handles (NEW | OLD . column_name) the new and old column names as transition variables ("transition variables") are created.

　For the INSERT statement, only NEW is legal; for the DELETE statement, only OLD is legal; and the UPDATE statement can be used with NEW and OLD at the same time. The following is an example of using NEW and OLD simultaneously in UPDATE.

CREATE TRIGGER t21_au

BEFORE UPDATE ON t22
FOR EACH ROW
BEGIN
SET @old = OLD . s1;
SET @new = NEW.s1;
END;//

Now if the s1 column in the t21 table The value is 55, then after executing "UPDATE t21 SET s1 = s1 + 1", the value of @old will become 55, and the value of @new will become 56.

Example of CREATE and INSERT CREATE table with trigger creates a table with trigger

In all the routines here, I assume that your delimiter has been set to // (DELIMITER //).

CREATE TABLE t22 (s1 INTEGER)//

CREATE TRIGGER t22_bi

BEFORE INSERT ON t22
FOR EACH ROW

BEGIN

SET @x = 'Trigger was activated!';
SET NEW.s1 = 55;
END ;//

　At the beginning, I created a table named t22, and then created a trigger t22_bi on table t22. When we want to insert a row in the table, the trigger will be activated, and the execution will Action to change the value of column s1 to 55.

　　INSERT on table with a trigger uses a trigger to perform the insert action

mysql> INSERT INTO t22 VALUES (1)//

　　Let us see what will happen if a row of data is inserted into table t2 and the table corresponding to the trigger ?

　The inserted action here is very common, and we do not need trigger permissions to execute it. You don't even need to know if there is a trigger associated.

mysql> SELECT @x, t22.* FROM t22//

+------------------------+------+

| @x | s1 |

+------------------------+------+
| Trigger was activated! | 55 |
+------------------------+------+
1 row in set (0.00 sec)

You can see INSERT The result after the action is as we expected, the x mark has been changed, and the data inserted here is not the inserted data we entered at the beginning, but the trigger's own data.

Example of a "check" constraint

"check" integrity constraint example

　　What's a "check" constraint What is a "check" constraint

　In standard SQL language, we can create a table in (CREATE TABLE) Use "CHECK (condition)" in the process,

For example:

CREATE TABLE t25
(s1 INT, s2 CHAR(5), PRIMARY KEY (s1),
CHECK (LEFT(s2,1)='A'))
ENGINE=INNODB;

Here CHECK means " When the leftmost character of the s2 column is not 'A', the insert and update statements will be illegal." MySQL views do not support CHECK. I personally hope that it can support it. But if you really need to use such a function in a table, I recommend that you use triggers to achieve it.

CREATE TABLE t25
(s1 INT, s2 CHAR(5),
PRIMARY KEY (s1))
ENGINE=INNODB//

CREATE TRIGGER t25_bi
BEFORE INSERT ON t25
FOR EACH ROW
IF LEFT( NEW. s2,1)<>'A' THEN SET NEW.s1=0; END IF;//

CREATE TRIGGER t25_bu
BEFORE UPDATE ON t25
FOR EACH ROW
IF LEFT(NEW.s2,1)< >'A' THEN SET NEW.s1=0; END IF;//

　 I only need to use the BEFORE INSERT and BEFORE UPDATE statements. Deleting the trigger will not affect the table, and the AFTER trigger will not affect the table. NEW process variables (transition variables) cannot be modified. In order to activate the trigger, I inserted data with s1=0 into the rows in the table. After that, any action that meets the LEFT(s2,1) <> 'A' condition will fail:

INSERT INTO t25 VALUES (0,'a') /* priming the pump */ //
INSERT INTO t25 VALUES (5,'b') /* gets error '23000' */ //

Don't Believe The Old MySQL Manual
It’s time to throw away the old MySQL manuals

I am here to warn you not to believe what the MySQL manuals of the past said. We have removed the incorrect statements about triggers, but there are still many old versions of the manual online. For example, this is a German URL:

　http://dev.mysql.com/doc /mysql/de/ANSI_diff_Triggers.html.

　This manual says that triggers are stored procedures. Forget it. You have already seen it. Triggers are triggers, and stored procedures are still stored procedures.

　The manual also says that triggers can be deleted from other tables, or fired when you delete a transaction. Whatever he means, forget it, MySQL will not implement this.

Finally, it is also wrong to say that using triggers will affect the query speed. Triggers will not have any impact on the query.

　　Bugs
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　ue will not be translated)
　　　On December 14 2004, I did an "Advanced Search" in 　http://bugs.mysql.com　for 'trigger' or
　　'triggers', I found that there were 17 active bugs as of that date. Of course they might disappear
　before you read this, but just in case they haven't, I'll mention the important ones. If they're still
there, you'll have to work around them when you're trying triggers.

　　Bug#5859 DROP TABLE does not drop triggers.
　　When you drop a table, dropping the table's triggers should be automatic You have to say "DROP TRIGGER < ;table name> . ".
　The correct way is "DROP TRIGGER ".

　　Bug#5894 Triggers with altered tables cause corrupt databases.
　　Will cause database data to be destroyed)
　　Do not alter a table that has a trigger on it, until you know this is fixed.

Conclusion Finally

　At the end of the book, I don’t think there is a need to review or re-read it for everyone. Let’s take a look, because I believe everyone can easily remember what I said above.

The above is the content of the MySQL 5.0 new feature tutorial Trigger: Lecture 1. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!

Statement：

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Previous article：Detailed explanation of MySQL database functions (4)Next article：Detailed explanation of MySQL database functions (4)

See more

MySQL 5.0 New Features Tutorial Triggers: Lecture 1

Related articles