Shulou(Shulou.com)05/31 Report--

This article is about how to analyze the triggers and events in the MySQL foundation. The editor thinks it is very practical, so I share it with you. I hope you can get something after reading this article.

Trigger

When we use MySQL, we may have the following requirements:

Automatically check the data before inserting or updating the data into the T1 table, requiring that the value of the M1 column be between 1 and 10. The verification rules are as follows:

If the value of the M1 column of the inserted record is less than 1, press 1 to insert.

If the value of M1 column is greater than 10, press 10 to insert.

The record is automatically inserted into the T2 table after the record is inserted into the T1 table.

That is, we may need to let the MySQL server execute some additional statements automatically before and after adding, deleting and changing the records in the table. This is the application scenario of the so-called trigger.

Create trigger

Let's take a look at the statement that defines the trigger:

CREATE TRIGGER trigger name {BEFORE | AFTER} {INSERT | DELETE | UPDATE} ON table name FOR EACH ROWBEGIN trigger content END

Tips:

A statement wrapped in curly braces `{}` and separated by vertical bar `|` indicates that a value must be selected among the given options. For example, `{BEFORE | AFTER}` means that a value must be selected between `BEFORE` and `After`.

Where {BEFORE | AFTER} indicates the timing of trigger content execution, and their meanings are as follows:

The name description BEFORE indicates that the contents of the trigger start before the specific statement is executed AFTER means that the contents of the trigger begin to be executed after the specific statement is executed

{INSERT | DELETE | UPDATE} indicates a specific statement. Currently, triggers can only be set for INSERT, DELETE and UPDATE statements in MySQL.

FOR EACH ROW BEGIN... END means that each record affected by a specific statement executes our custom trigger content:

For the INSERT statement, the records that FOR EACH ROW affects are the new records that we are going to insert.

For DELETE statements and UPDATE statements, the records affected by FOR EACH ROW are those that meet the WHERE condition (if there is no WHERE condition in the statement, it represents the entire record).

Tip: if the trigger contains only one statement, you can also omit the words BEGN and END.

Because the MySQL server calls our custom trigger contents for all records affected by a statement in turn, we need a way to access the contents of each affected record. MySQL provides the words NEW and OLD to represent the new record and the old record, respectively, and they have different meanings in different statements:

For triggers set by the INSERT statement, NEW represents the record to be inserted, and OLD is invalid.

For triggers set by the DELETE statement, OLD represents the record before deletion, and NEW is invalid.

For triggers set by the UPDATE statement, NEW represents the modified record and OLD represents the pre-modified record.

Now we can formally define a trigger:

Mysql > delimiter $mysql > CREATE TRIGGER bi_t1-> BEFORE INSERT ON T1-> FOR EACH ROW-> BEGIN-> IF NEW.m1

SET NEW.m1 = 1;-> ELSEIF NEW.m1 > 10 THEN-> SET NEW.m1 = 10;-> END IF;-> END $Query OK, 0 rows affected (0.02 sec) mysql > delimiter; mysql >

We define a trigger called bi_t1 for the T1 table, which means that BEGIN is executed for each record to be inserted before inserting a new record into the T1 table. The statement between END, NEW. The column name represents the value of the specified column of the current record to be inserted. There are now four records in the T1 table:

Mysql > SELECT * FROM T1 + | M1 | N1 | +-+-+ | 1 | a | 2 | b | 3 | c | 4 | d | +-+-+ 4 rows in set (0.00 sec) mysql >

Let's now execute the insert statement and look at the contents of the T1 table again:

Mysql > INSERT INTO T1 (M1, N1) VALUES (5,'e'), (100,z'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0mysql > SELECT * FROM T1 +-+-+ | M1 | N1 | +-+-+ | 1 | a | 2 | b | 3 | c | 4 | d | 5 | e | 10 | z | +-+-+ 6 rows in set (0.00 sec) mysql >

This INSERT statement affects two records, (5,'e') and (100,'z'), which will execute our custom trigger content, respectively. It is obvious that (5,'e') is successfully inserted into the T1 table, while (100,'z') is inserted into the table and becomes (10,'z'), which means that our bi_t1 trigger works!

Tip: the `bi` of the trigger name `before t1` defined above is the acronym of `before insert`, and `t1` is the table name. Although there are no special requirements for the naming of triggers, it is recommended that you define the form in my example above, namely, `bi_ table name`, `bd_ table name`, `bu_ table name`, `ai_ table name`, `ad_ table name` and `au_ table name`.

The above is just an example of setting BEFORE triggers for INSERT statements. The process of setting BEFORE or AFTER triggers for DELETE and UPDATE operations is similar, so I won't repeat it.

View and delete triggers

View the statements for all triggers defined in the current database:

SHOW TRIGGERS

View the definition of a specific trigger:

SHOW CREATE TRIGGER trigger name

Delete trigger:

DROP TRIGGER trigger name

These commands are too simple, let's not give examples.

Considerations for the use of triggers

There can be no statement in the trigger content that outputs the result set.

For example:

Mysql > delimiter $mysql > CREATE TRIGGER ai_t1-> AFTER INSERT ON T1-> FOR EACH ROW-> BEGIN-> SELECT NEW.m1, NEW.n1;-> END $ERROR 1415 (0A000): Not allowed to return a result set from a triggermysql >

The ERROR displayed means that the result set is not allowed to be returned in the trigger content!

The value of the column represented by NEW in the contents of the trigger can be changed, and the value of the column represented by OLD cannot be changed.

NEW represents a newly inserted or soon-to-be modified record, changing the value of its column will affect the result after the execution of INSERT and UPDATE statements, while OLD represents the value before it is modified or deleted, and we cannot modify it. For example, if we have to write like this, we will make a mistake:

Mysql > delimiter $mysql > CREATE TRIGGER bu_t1-> BEFORE UPDATE ON T1-> FOR EACH ROW-> BEGIN-> SET OLD.m1 = 1;-> END $ERROR 1362 (HY000): Updating of OLD row is not allowed in triggermysql >

You can see that the record represented by OLD in the trigger shown in the error prompt is immutable.

In BEFORE triggers, we can use SET NEW. Column name = a value to change the value of a column of a record to be inserted or updated, but this cannot be used in an AFTER trigger because the record has been inserted or updated when the contents of the AFTER trigger are executed.

For example, if we have to write like this, we will make a mistake:

Mysql > delimiter $mysql > CREATE TRIGGER ai_t1-> AFTER INSERT ON T1-> FOR EACH ROW-> BEGIN-> SET NEW.m1 = 1;-> END $ERROR 1362 (HY000): Updating of NEW row is not allowed in after triggermysql >

You can see that the error indicated in the prompt does not allow you to change the record represented by NEW in the AFTER trigger.

If we encounter an error during the execution of the content of our BEFORE trigger, the specific statement corresponding to the trigger will not be executed; if an error is encountered during the execution of the specific operation statement, then the content of the corresponding AFTER trigger will not be executed.

Tip: for tables that support transactions, all statements in the process will be rolled back, whether there is an error in the execution of the trigger content or the specific operation statement. Of course, as rookies, we don't know what a transaction is or what a rollback is. These advanced contents are all in "how MySQL works: understanding MySQL from the Root."

Event

Sometimes we need to create an event when we want the MySQL server to execute statements automatically at some point in time or at regular intervals.

Create an event

The syntax for creating an event is as follows:

CREATE EVENT event name ON SCHEDULE {AT a certain point in time | EVERY expected time interval [STARTS datetime] [END datetime]} DOBEGIN specific statement END

Events support two types of automatic execution:

Execute at a certain point in time.

For example:

CREATE EVENT insert_t1_eventON SCHEDULEAT '2019-09-04 15:48:54'DOBEGIN INSERT INTO T1 (M1, N1) VALUES (6,' f'); END

In this event, we specify that the execution time is' 2019-09-04 15-48-14-14. Besides filling in a certain time constant directly, we can also fill in some expressions:

CREATE EVENT insert_t1ON SCHEDULEAT DATE_ADD (NOW (), INTERVAL 2 DAY) DOBEGIN INSERT INTO T1 (M1, N1) VALUES (6,'f'); END

Where DATE_ADD (NOW (), INTERVAL 2 DAY) indicates that the event will be executed two days after the current time.

It is executed at regular intervals.

For example:

CREATE EVENT insert_t1ON SCHEDULEEVERY 1 HOURDOBEGIN INSERT INTO T1 (M1, N1) VALUES (6,'f'); END

Where EVERY 1 HOUR indicates that the event will be executed every hour. By default, this method of executing at regular intervals will continue indefinitely, starting with the event that created the event. We can also specify the start and end time of the event:

CREATE EVENT insert_t1ON SCHEDULEEVERY 1 HOUR STARTS '2019-09-04 15 ENDS 48 15:48:54'DOBEGIN INSERT INTO 54' ENDS' 2019-09-16 15:48:54'DOBEGIN INSERT INTO T1 (M1, N1) VALUES (6,'f'); END

As shown above, the event will start from '2019-09-04 15-15-48-lac-54' to '2019-09-16 15-15-48-14', and will be executed every hour in between.

Tip: in addition to HOUR, you can also use YEAR, QUARTER, MONTH, DAY, HOUR, MINUTE, WEEK, SECOND, YEAR_MONTH, DAY_HOUR, DAY_MINUTE, DAY_SECOND, HOUR_MINUTE, HOUR_SECOND, MINUTE_SECOND to choose the time interval units we need according to your specific needs.

After the event is created, we don't have to worry about it. At the specified time, the MySQL server will automatically execute it for us.

View and delete events

View statements for all events defined in the current database:

SHOW EVENTS

View the definition of a specific event:

SHOW CREATE EVENT event name

Delete event:

DROP EVENT event name

These commands are too simple, let's not give examples.

Event usage considerations

By default, the MySQL server does not help us execute events unless we manually turn on the feature using the following statement:

Mysql > SET GLOBAL event_scheduler = ON;Query OK, 0 rows affected (0.00 sec) mysql >

Tip: event_scheduler is actually a system variable, and its value can also be set by the startup parameter or through the configuration file when the MySQL server is started. These so-called system variables, startup parameters and configuration files are not what we rookies need to master now, just ignore them.

The above is how to analyze the triggers and events in the foundation of MySQL. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.