MySQL 5.0 triggers refer to the tutorial

Conventions and Styles conventions and programming styles
Every time I wanted to demonstrate the actual code, I tweaked the code that appeared on the mysql client's screen, changing the font to Courier to make it look like plain text (let's distinguish between the program code and the text). Here's an example:

mysql > DROP FUNCTION f;
Query OK, 0 rows affected (0.00 sec)
If the instance is large, you'll need to comment between lines and paragraphs, and I'll use "will" < "--" symbols are placed on the right side of the page for emphasis. Such as:

mysql > CREATE PROCEDURE p ()
- > BEGIN
- > /* This procedure does nothing */ < --
- > END;//
Query OK, 0 rows affected (0.00 sec)
Sometimes I put the "mysql "in the example > "And" - > "Without these system displays, you can copy the code directly to the mysql client program (if what you are reading is not an electronic version, you can download the script from the mysql.com website).

All the examples have been tested in Suse 9.2 Linux and Mysql 5.0.3 public. By the time you read this book, Mysql is already available in higher editions and supports more OS, including Windows, Sparc, HP-UX. So this example will work fine on your computer. But if things still go wrong, consult with a senior Mysql user you know so you can get better support and help.

Why do Why Triggers use triggers

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

Users of earlier versions of MySQL have long required triggers.

We promised to support all features of the ANSI standard.

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

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

You can monitor the action of data changes in 1 session.


I'm going to assume that you've all read the first episode of the "new MySQL features" series, "MySQL stored procedures, "and you should all know about MySQL stored procedures and functions, which is important because in triggers you can use the same statements that you used in the functions. Here's a special 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 assignments (SET) are legal.

Allow condition declaration.

Exception handling declarations are also allowed.

However, keep in mind that the function has a constraint: the table cannot be accessed in the function, so it is illegal to use the following statement in the 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'
SHOW 'START TRANSACTION' TRUNCATE UPDATE
There is also a one-of-a-kind restriction in triggers.

Trigger is relatively new, so there will be defects (bugs). So I give you warning here, as I said in the stored procedure book. Don't use this in contains important data in the database triggers, if need be on 1 some to test the database for the purpose of use, at the same time when you are on the table create a trigger confirmed that the database is the default.

Syntax grammar
1. Syntax: Name syntax: naming rules
CREATE TRIGGER < Trigger name > < --
{ BEFORE | AFTER }
{ INSERT | UPDATE | DELETE }
ON < The name of the table >
FOR EACH ROW
< Trigger SQL statement >
The trigger must have a name, up to 64 characters, possibly followed by a delimiter; it is named much like other objects in MySQL.


Here I have a habit of using the name of the table +'_'+ short for the trigger type, so if it is table t26 and the trigger is before (BEFORE) the event UPDATE (refer to the points (2) and (3) below), then its name is t26_bu.

Syntax: Time syntax: trigger time

CREATE TRIGGER < Trigger name >
{ BEFORE | AFTER } < --
{ INSERT | UPDATE | DELETE }
ON < The name of the table >
FOR EACH ROW
< The SQL statement that fires >
The trigger has a time setting for execution: it can be set to before or after the event.

Syntax: Event syntax: events

CREATE TRIGGER < Trigger name >
{ BEFORE | AFTER }
{ INSERT | UPDATE | DELETE } < --
ON < The name of the table >
FOR EACH ROW
< Triggered SQL statement >
It is also possible to set triggered events: they can be triggered during the execution of insert, update, or delete.

4. Syntax: Table: table

CREATE TRIGGER < Trigger name >
{ BEFORE | AFTER }
{ INSERT | UPDATE | DELETE }
ON < The name of the table > < --
FOR EACH ROW
< Triggered SQL statement >
A trigger is a member of a table that causes the trigger to be activated when an insert, update, or delete operation is performed on that table.

We cannot assign two triggers to the same event in the same table.

Syntax: Granularity syntax: (step size) trigger interval

CREATE TRIGGER < Trigger name >
{ BEFORE | AFTER }
{ INSERT | UPDATE | DELETE }
ON < The name of the table >
FOR EACH ROW < --
< Triggered SQL statement >
Trigger execution interval: the FOR EACH ROW clause tells the trigger to perform an action every other row instead of once for the entire table.


6. Syntax: Statement syntax: statement

CREATE TRIGGER < Trigger name >
{ BEFORE | AFTER }
{ INSERT | UPDATE | DELETE }
ON < The name of the table >
FOR EACH ROW
< The SQL statement that fires > < --
The trigger contains the SQL statement to be fired: the statement here can be any valid statement, including a compound statement, but the statement here is subject to the same restrictions as the function.

Privileges permissions

You must have considerable permissions to create triggers (CREATE TRIGGER). If you're already an Root user, that's enough. This is different from the standard of SQL, and I hope to change it to the standard soon.

So in the next version of MySQL, it's entirely possible that you'll see a new permission called CREATE TRIGGER. And then through this method:

GRANT CREATE TRIGGER ON < The name of the table > TO < User or user list > ;

You can also withdraw permissions by:

REVOKE CREATE TRIGGER ON < The name of the table > FROM < User or user list > ;

Referring to OLD and NEW columns

In the SQL statement of the trigger, you can associate any column in the table. But you can't just use the name of the column to identify it. That would confuse the system, because there might be a new name for the column (which you might want to change, and your action might be to change the column name), and the old name for the column. So you have to use this syntax:

"NEW.column_name" or "OLD.column_name ". This technically handles (NEW | OLD.column_name) the new and old column names belong to the created transition variable (transition variables).

For INSERT statements, only NEW is legal; For DELETE statements, only OLD is valid; The UPDATE statement can be used in conjunction with NEW and OLD. Here is an example of using both NEW and OLD 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 t21 has a value of 55, then after "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 and INSERT examples

CREATE table with trigger creates a table with triggers

In all of these routines, I'm going to assume that your delimiter is 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;//
I initially created a table with the name t22, and then created a trigger t22_bi on t22. When we insert into a row in the table, the trigger is activated to change the value of s1 column to 55.

INSERT on table w ith a trigger performs the insert action using a trigger

mysql > INSERT INTO t22 VALUES (1)//

Let's see what happens if we insert a row of data into the table t2 corresponding to the data trigger?

The insert action here is so common that we don't need the trigger's permission to execute it. You don't even need to know if there is a trigger association.

mysql > SELECT @x, t22.* FROM t22//
+------------------------+------+
| @x | s1 |
+------------------------+------+
| Trigger was activated! | 55 |
+------------------------+------+
1 row in set (0.00 sec)
As you can see from the INSERT action, as we expected, the x tag has been changed, and the inserted data here is not the inserted data that we entered at the beginning, but the trigger's own data.


Example of a "check" constraint

Example of "check" integrity constraint

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

In the standard SQL language, we can use "CHECK (condition)" when creating a table (CREATE TABLE),

Such as:

CREATE TABLE t25(s1 INT, s2 CHAR(5), PRIMARY KEY (s1),
CHECK (LEFT(s2,1)='A'))
ENGINE=INNODB;
CHECK here means "both insert and update statements are illegal when the leftmost character in s2 column is not 'A'. But if you really need to do this in a table, I recommend using triggers.

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;//
All I need to do is use BEFORE INSERT and BEFORE UPDATE statements, delete the trigger will not affect the table, and the trigger of AFTER cannot change the procedure variable of NEW (transition variables). To activate the trigger, I performed the insertion of s1 = 0 data into the rows in the table, and then as long as I performed LEFT(s2,1) < > The 'A' condition fails:

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 ditch the old MySQL manual

I'm here to warn you not to believe what the MySQL manual says. We have removed the error statement about the trigger, but there are still many old versions of the manual on the Internet. For example, here is one from Url in Germany:

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

The manual says that a trigger is a stored procedure, forget it, as you've seen, a trigger is a trigger, and a stored procedure is still a stored procedure.

The manual also says that triggers can be deleted from other tables, or fired when you delete a transaction, whatever that means, forget that MySQL is not going to implement that.

Finally, the statement that using triggers has an impact on query speed is also false; triggers do not have any impact on the query.

Bugs

(bad things don't translate)

On December 14, 2004, I did an "Advanced Search" in http: / / bugs mysql. com for trigger or

'triggers', I found there were 17 active bugs as of that they disappear

before you read this, but just case case case they case case they case they case

there, you'll to work around them when trying triggers

Bug#5859 DROP TABLE does not drop triggers

(there is no automatic delete trigger when deleting a table)

When you drop a table, dropping the table's triggers be automatic

Bug#5892 Triggers have the wrong namespace


(there is an error in the namespace of the trigger. You must first add the name of the table to remove the trigger. Here is an example.)

You have to say "DROP TRIGGER < table name > . < trigger name > ".

The correct way is "DROP TRIGGER < trigger name > ".

Bug#5894 Triggers with altered tables cause corrupt databases

(trigger changes to tables can cause database data to be corrupted)

Do not alter table that has trigger on it until you know this is fixed