What are the actual functions of the MySQL5 trigger?

This article mainly introduces the MySQL5 trigger. Every time I demonstrate the actual code, I will use MySQL (the best combination with PHP) the code displayed on the screen of the client is adjusted, mainly by changing the font to Courier, making them look different from the common text so that you can differentiate the program code and body ).

Here is an example of the MySQL5 trigger Tutorial:

MySQL (best combination with PHP)> drop function f; Query OK, 0 rows affected (0.00 sec) if the instance is large, you need to add comments between some rows and paragraphs, at the same time, I will place the "<--" symbol on the right of the page to emphasize it. For example:

MySQL (best combination with PHP)> create procedure p ()-> BEGIN->/* This procedure does nothing */<---> END; // Query OK, 0 rows affected (0.00 sec) Sometimes I will remove the "MySQL (the best combination with PHP)>" and "->" system display in the example, you can directly copy the code to the MySQL (the best combination with PHP) client program. If you are not reading an electronic version, you can use MySQL (the best combination with PHP ). so the examples have been tested on Suse 9.2 Linux and MySQL (the best combination with PHP) 5.0.3 public edition.

When you read this book, MySQL (the best combination with PHP) already has a higher version and supports more operating systems, including Windows, Linux, and HP-UX. So the example here will run normally on your computer. However, if the operation still fails, you can consult the senior MySQL users you know (the best combination with PHP) to get better support and help.

Why use a trigger?

In MySQL (the best combination of PHP and MySQL) 5.0, the support for triggers is due to the following reasons:

Early versions of MySQL (the best combination with PHP) require triggers for a long time. We have 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 suppose everyone has read "MySQL (the best combination with PHP) New Features" Series 1-"MySQL (the best combination with PHP) Stored Procedures ", so everyone should know that MySQL (the best combination with PHP) has stored procedures and functions, which is very important, because you can use the statements used in functions in triggers. For example:

The compound statement (BEGIN/END) is valid. flow control Flow-of-control) Statements (IF, CASE, WHILE, LOOP, WHILE, REPEAT, LEAVE, ITERATE) are also valid. variable Declaration (DECLARE) and assignment (SET) are legal. allow condition declaration. exception Handling statement is also allowed. however, remember that a function has a restriction: you cannot access a table in the function. therefore, the following statement is invalid in the function.

 
 

  
  
ALTER 'CACHE INDEX' CALL COMMIT CREATE DELETEDROP   
  
  
'FLUSH PRIVILEGES' GRANT INSERT KILLLOCK OPTIMIZE REPAIR REPLACE REVOKEROLLBACK   
  
  
SAVEPOINT 'SELECT FROM table''SET system variable' 'SET TRANSACTION'SHOW 'START TRANSACTION'  
  
  
TRUNCATE UPDATE  
 
 

The same restrictions apply to the MySQL5 trigger. the trigger is relatively new, so there will be a bugs) defect. so I am here to give you a warning, as I said in the stored procedure book. do not use this trigger in databases that contain important data. If necessary, use the trigger in some test-oriented databases and confirm that these databases are default when you create a trigger on a table.

Syntax

1. syntax: naming rules: create trigger <TRIGGER Name> <-- {BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON <Table Name> for each row <trigger SQL statement> the TRIGGER must have a name, A maximum of 64 characters, which may be followed by delimiters. it is similar to the naming method of other objects in MySQL (the best combination with PHP.

Here I have a habit: using the table name + '_' + the abbreviation of the trigger type. therefore, for table t26, if the trigger is BEFORE event UPDATE, its name is t26_bu.

2. Syntax:

TRIGGER time: create trigger <TRIGGER Name> {BEFORE | AFTER} <-- {INSERT | UPDATE | DELETE} ON <Table Name> for each row <triggered SQL statement> TRIGGER executed time Setting: it can be set to before or after an event.

3. syntax: event create trigger <TRIGGER Name> {BEFORE | AFTER} {INSERT | UPDATE | DELETE} <-- ON <Table Name> for each row <triggered SQL statement> event: they can be triggered when an insert, update, or delete operation is executed.

4. syntax: table create trigger <TRIGGER Name> {BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON <Table Name> <-- for each row <triggered SQL statement> the TRIGGER belongs to table: when the insert, update, or delete operation is performed on the table, the trigger is activated. we cannot schedule two triggers for the same event of the same table.

5. syntax: Step Size) TRIGGER interval create trigger <TRIGGER Name> {BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON <Table Name> for each row <-- <triggered SQL statement> TRIGGER execution Interval: the for each row clause notifies the trigger to execute an action every ROW instead of the entire table.

6. syntax: statement create trigger <TRIGGER Name> {BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON <Table Name> for each row <triggered SQL statement> <-- TRIGGER contains SQL statement: the statement here can be any legal statement, including compound statements, but the statements here are subject to the same restrictions as the functions. Privileges permissions: You must have considerable permissions to CREATE the trigger create trigger ).

If you are a Root user, it is enough. This is different from the SQL standard. Therefore, in the next version of MySQL (the best combination with PHP), you may see a new permission called create trigger. GRANT the following permissions to grant: grant create trigger on <Table Name> TO <user or user list>; you can also revoke permissions as follows: revoke create trigger on <Table Name> FROM <user or user list>; ID of the old and newly created Columns

In the SQL statement of the trigger, you can associate any column in the table. However, you cannot only use the column name to identify it, which will confuse the system, because there may be a new column name, which may be exactly what you want to modify, your action may be to modify the column name), and the old name of the Column exists. Therefore, you must use this syntax to mark: "NEW. column_name "or" OLD. column_name ". in this way, NEW | OLD is processed technically. column_name) the New and Old column names belong to the created transition variable "transition variables ").

For INSERT statements, only NEW statements are valid; For DELETE statements, only OLD statements are valid; and UPDATE statements can be used together with NEW and OLD statements. The following is an example of using both NEW and OLD in UPDATE.

 
 

  
  
CREATE TRIGGER t21_auBEFORE UPDATE ON t22FOR EACH ROWBEGINSET @old = OLD . s1;SET @new = NEW.s1;END; 
 
 

If the value of column s1 in Table t21 is 55, the value of @ old is 55 after "UPDATE t21 SET s1 = s1 + 1" is executed, the value of @ new will change to 56. The Example of CREATE and INSERT creates a table with the MySQL5 trigger. In all the routines, I assume that the DELIMITER has been set to // DELIMITER //).

Create table t22 (s1 INTEGER) // create trigger t22_biBEFORE insert on t22FOR each rowbeginset @ x = 'trigger was activated! '; Set new. s1 = 55; END; // At the beginning, I created a table named t22 and created a trigger t22_bi on table t22, when we want to insert rows into the table, the trigger will be activated and change the value of column s1 to 55.

Use a trigger to execute the insert action

MySQL (best combination with PHP)> insert into t22 VALUES (1 )//

Let's see what happens if we insert a data trigger row to table t2? The insert action here is very common. We do not need the trigger permission to execute it. You do not even need to know whether a trigger is associated.

 
 

  
  
MySQL (best combination with PHP)> SELECT @ x, t22. * FROM t22 // + ---------------------- + ------ + |
  
  
@ X | s1 | + ---------------------- + ------ + | Trigger was activated! | 55 | + ------------------------ + ------ +
  
  
1 row in set (0.00 sec)
 
 

We can see the results after the INSERT action. As we expected, the x mark is changed. At the same time, the inserted data is not the data we started to INSERT, but the data of the trigger.

"Check" integrity constraints example what is "check" constraints in the standard SQL language, we can use "CHECK (condition)" when creating a TABLE in CREATE TABLE, for example: create table t25 (s1 INT, s2 CHAR (5), Prima (the most complete VM management system) ry key (s1), CHECK (LEFT (s2, 1) = 'A') ENGINE = INNODB; CHECK indicates that "the insert and update statements are invalid if the leftmost character of column s2 is not 'a ", the view of MySQL (the best combination with PHP) does not support CHECK. I personally hope it can support it.

However, if you need to use such a function in a table, we recommend that you use a trigger.

 
 

  
  
Create table t25 (s1 INT, s2 CHAR (5 ),
  
  
Prima (the most comprehensive VM management system) ry key (s1) ENGINE = INNODB // create trigger t25_biBEFORE I
  
  
Nsert on t25FOR each rowif left (NEW. s2, 1) <> 'A' then set new. s1 = 0; end if;
  
  
Create trigger t25_buBEFORE update on t25FOR each rowif 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 a trigger does not affect the table, and the AFTER trigger cannot modify the NEW procedure variable transition variables ). To activate the trigger, I inserted data s1 = 0 to the rows in the table. After that, all operations that meet 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 '000000' * // Don't Believe The Old MySQL (The best combination with PHP) Manual: discard The Old MySQL (The best combination with PHP) the Manual

I am here to warn you not to trust the previous MySQL (best combination with PHP) Manual

The above content is an introduction to the MySQL5 trigger tutorial. I hope you will get something.