MySQL 5.0 New Features Tutorial Triggers: Lecture 1
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
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
Permissions can also be revoked like this:
REVOKE CREATE TRIGGER ON
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_auBEFORE 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_biBEFORE INSERT ON t22
FOR EACH ROW
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
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.
+------------------------+------+
| @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
In standard SQL language, we can create a table in (CREATE TABLE) Use "CHECK (condition)" in the process,
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.
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
AI Hentai Generator
Generate AI Hentai for free.
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
1377
52
MySQL: The Ease of Data Management for Beginners
Apr 09, 2025 am 12:07 AM
MySQL is suitable for beginners because it is simple to install, powerful and easy to manage data. 1. Simple installation and configuration, suitable for a variety of operating systems. 2. Support basic operations such as creating databases and tables, inserting, querying, updating and deleting data. 3. Provide advanced functions such as JOIN operations and subqueries. 4. Performance can be improved through indexing, query optimization and table partitioning. 5. Support backup, recovery and security measures to ensure data security and consistency.
Can I retrieve the database password in Navicat?
Apr 08, 2025 pm 09:51 PM
Navicat itself does not store the database password, and can only retrieve the encrypted password. Solution: 1. Check the password manager; 2. Check Navicat's "Remember Password" function; 3. Reset the database password; 4. Contact the database administrator.
How to create navicat premium
Apr 09, 2025 am 07:09 AM
Create a database using Navicat Premium: Connect to the database server and enter the connection parameters. Right-click on the server and select Create Database. Enter the name of the new database and the specified character set and collation. Connect to the new database and create the table in the Object Browser. Right-click on the table and select Insert Data to insert the data.
MySQL: Simple Concepts for Easy Learning
Apr 10, 2025 am 09:29 AM
MySQL is an open source relational database management system. 1) Create database and tables: Use the CREATEDATABASE and CREATETABLE commands. 2) Basic operations: INSERT, UPDATE, DELETE and SELECT. 3) Advanced operations: JOIN, subquery and transaction processing. 4) Debugging skills: Check syntax, data type and permissions. 5) Optimization suggestions: Use indexes, avoid SELECT* and use transactions.
MySQL and SQL: Essential Skills for Developers
Apr 10, 2025 am 09:30 AM
MySQL and SQL are essential skills for developers. 1.MySQL is an open source relational database management system, and SQL is the standard language used to manage and operate databases. 2.MySQL supports multiple storage engines through efficient data storage and retrieval functions, and SQL completes complex data operations through simple statements. 3. Examples of usage include basic queries and advanced queries, such as filtering and sorting by condition. 4. Common errors include syntax errors and performance issues, which can be optimized by checking SQL statements and using EXPLAIN commands. 5. Performance optimization techniques include using indexes, avoiding full table scanning, optimizing JOIN operations and improving code readability.
How to create a new connection to mysql in navicat
Apr 09, 2025 am 07:21 AM
You can create a new MySQL connection in Navicat by following the steps: Open the application and select New Connection (Ctrl N). Select "MySQL" as the connection type. Enter the hostname/IP address, port, username, and password. (Optional) Configure advanced options. Save the connection and enter the connection name.
How to open phpmyadmin
Apr 10, 2025 pm 10:51 PM
You can open phpMyAdmin through the following steps: 1. Log in to the website control panel; 2. Find and click the phpMyAdmin icon; 3. Enter MySQL credentials; 4. Click "Login".
How to execute sql in navicat
Apr 08, 2025 pm 11:42 PM
Steps to perform SQL in Navicat: Connect to the database. Create a SQL Editor window. Write SQL queries or scripts. Click the Run button to execute a query or script. View the results (if the query is executed).
