“This is the 16th day of my participation in the Gwen Challenge in November. Check out the details: The Last Gwen Challenge in 2021.”

Hi, family, I’m bug bug, here I go again. Let’s talk about something today. OK, let’s continue with the Ubuntu series. Ha ha ha ha, said that I am used to it, recently a friend of mine private message, always tired of Watching Ubuntu, no problem, today a week to give you a different taste, you can listen to learn ha.

It has good teaching value. I hope you can gain something from this article. I suggest you collect it first and then read it.

If you think the article is good, please like it, bookmark it, comment on it, and share it together. Remember to give the bug bacteria a three-link with one button

Therefore, for xiaobai series of teaching, Bug bacteria has opened a special column, interested friends can pay attention to “Ubuntu Zero-based teaching”.

Bug bacteria do so much just for one thing is to want to teach you all, teach will not charge tuition! For those who are helpful to you, please don’t forget to give a thumbs-up to bug bacteria. Your encouragement is the biggest support for me! So then, business as usual! The bugs are going to start class

Article Contents:

I. Introduction:

Ii. Preface:

Iii. Contents of this Chapter:

1. What is a MySQL trigger?

#1 Basic Concepts

# 2:

Version # 3:

2. Why use MySQL trigger?

3. How to create MySQL trigger?

#1 Basic syntax

#2 Trigger time

#3 Trigger the event

#4 Precautions

#5 Example

#6 Trigger execution order

4. MySQL trigger application

5. Advantages and disadvantages of MySQL triggers

Advantages of # 1

# 2 faults

4. Common commands

Personal advice

Six, hot article recommendation

I. Environmental description

System requirements: Ubuntu20.04

Second, the preface

MySQL, one of the most popular relational database management systems, is developed by MySQL AB in Sweden and belongs to Oracle. Since MySQL is already source-sourced, the cost is greatly reduced, but you can also purchase a commercial license version from Oracle for advanced support services (for special enterprise users).

Compared to other database software, such as Oracle Database or Microsoft SQL Server, MySQL is already very easy to learn and master. MySQL can run on UNIX, Linux, Windows, etc. It can also be installed on a server or even a desktop system. In addition, MySQL is reliable, extensible and fast. If you are developing Web sites or Web applications, MySQL is a good choice (highly recommended). MySQL is an important part of the LAMP stack, including Linux, Apache, MySQL, and PHP. For more information about how to use mysql, please visit the official website.

MySQL :: Download MySQL Community Server

​

Iii. Preface:

Mysql has transactions, indexes, triggers, stored procedures, etc. So today, bug bacteria today to give you a talk about it

But not for long, a week or two at most… O pooh, a day or two; This period is also for the sake of the promise of private chat bug friends, a separate session of mysql trigger related basic teaching; Also please small friends fine taste ~

At the same time if it is found on the way to read a text about wrong or understand there is deviation, also a lot of friends, please understand, after all bugs bacteria also experience level is limited, but very welcome friends can leave opinions, put forward valuable Suggestions and comments, the comment below, find out my shortcomings, bug bacteria growth is also a witness your growth! Do your best! Do your best.

** If you find this article helpful, please don’t hesitate to give your thumbs up, and pIA light up is done!! ** Your encouragement is the biggest support for bug fungus writing!

So then, business as usual! The bugs are going to start class

​

Iv. Contents of this Chapter:

1. What is a MySQL trigger?

#1 Basic Concepts

  • Trigger is a special type of stored procedure, which is different from stored procedure. It is mainly executed by triggering events, that is, it is not actively invoked. Stored procedures, on the other hand, need to actively invoke their name to execute.
  • Trigger: Binds a code to a table in advance. When certain contents in the table change (add, delete, change), the system will automatically trigger the code and execute it.

# 2:

  • Data can be forcibly checked or converted before being written (to ensure data security). When a trigger fails, the previous user’s successful operation is undone, similar to the rollback of a transaction.

Version # 3:

  • Stored procedures and triggers are only supported in MySQL 5.0

2. Why use MySQL trigger?

  • Triggers can check or modify new data values, which means that triggers can be used to enforce data integrity, such as checking if a percentage value is between 0 and 100, and to perform necessary filtering on input data.
  • Triggers can assign the result of an expression to a data column as a default value. So we can get around the constraint that the default value in the column definition must be constant;
  • Triggers can examine the current contents of a data row before deleting or modifying it. This capability enables many functions, such as logging changes to existing data in a log.

​

3. How to create MySQL trigger?

#1 Basic syntax

Delimiter Custom end symbol Create trigger Trigger name Trigger time Trigger event on table for each row BEGIN -- Trigger content body, each line ends with a semicolon (;) end The custom end conforms to Delimiter.Copy the code

On table for each: trigger object, trigger binding is essentially all rows in the table, so when each row is specified to change, trigger occurs;

Among them:

  1. Trigger name: Identifies the trigger name, which can be specified by the user.
  2. Trigger time: indicates the trigger time. The value can be BEFORE or AFTER.
  3. Trigger event: Identifies the trigger event. The value can be INSERT, UPDATE, or DELETE.
  4. Trigger table name: identifies the table name on which the trigger is to be created.
  5. Trigger body: It can be a single SQL statement or multiple statements with BEGIN and END.

BEFORE INSERT, BEFORE UPDATE, BEFORE DELETE, AFTER INSERT, AFTER UPDATE, AFTER DELETE.

#2 Trigger time

When an SQL instruction occurs, the data in the row changes, and the corresponding row in each table has two states: before and after the data operation

  • Before: Indicates the state before the data in the table changes
  • After: Indicates the status of the data in the table after it is changed

PS: If the before trigger fails or the statement itself fails, the After trigger (if any) will not execute

#3 Trigger the event

Triggers are triggered only when data is sent to change, and the corresponding operation is only

  • INSERT
  • DELETE
  • UPDATE

#4 Precautions

In MySQL 5, trigger names must be unique in each table, but not in each database, meaning that two tables in the same database may have triggers with the same name

Only one trigger is allowed per event per table at a time, so a maximum of six triggers can be supported per table

Before /after insert, before/after delete, before/after update

Another limitation is that you cannot create two triggers of the same type on a table at the same time, so you can create a maximum of six triggers on a table.

​

#5 Example

Create a complete trigger.

I’ll start by creating a full trigger called trigger_demo;

After an update operation is performed on the user table, a log is automatically inserted into the log_info table. The log records the operation time, the user name and the user ID before the update.

delimiter || 
create trigger trigger_demo AFTER UPDATE ON user for each row
BEGIN
		INSERT into log_info ( create_time,user_id,user_name )VALUES (now(), old.user_id,old.user_name);
END || 
	delimiter;
Copy the code

A. Detailed explanation of variables

Delimiter is used in MySQL to define a local variable that can only be used in BEGIN… END is used in the compound statement, and should be defined at the beginning of the compound statement,

By default, delimiter is a semicolon; In the command line client, if a command line ends with a semicolon, mysql will execute the command after press enter.

delimiter || 
Copy the code

The DELIMITER pronounce good terminator “| |”, and then finally is defined as “;” MYSQL default terminator is “;” .

B. NEW and OLD

The NEW keyword is used in the above example, and similar to INSERTED and DELETED in MS SQL Server, NEW and OLD are defined in MySQL to denote

The row in the table that triggers the trigger. Specifically:

  • In INSERT triggers, NEW is used to indicate NEW data to be inserted BEFORE or AFTER.
  • In UPDATE triggers, OLD is used to represent OLD data that will or has been modified, and NEW is used to represent NEW data that will or has been modified to.
  • In DELETE triggers, OLD is used to indicate the original data that will or has been deleted.

Usage: New. columnName (columnName is the name of a column in the corresponding table) In addition, old is read-only, while NEW can be assigned to a set in a trigger so that it does not trigger the trigger again and cause a circular call (for example, every time a user is inserted, Both concatenate “20210617” in front of their user code, which defines a temporary variable using set.

Define a temporary variable, usually named with the @ prefix, such as @new_user_id; Then use it directly in the event SQL you want to execute!

delimiter || 
create trigger trigger_demo_update AFTER update ON sys_user for each row
BEGIN
	SET @new_user_id = CONCAT('20210617',new.id);
	INSERT into log_info ( create_time,user_id,user_name )VALUES (now(), @new_user_id,new.user_name);
END || 
delimiter;
Copy the code

Presentation:

  • Test update to get data before and after the update

    UPDATE user SET user_name= ‘alter user ‘,user_id=’999′ where user_id=’ 30

    delimiter || create trigger trigger_demo_update AFTER UPDATE ON user for each row BEGIN INSERT into log_info ( create_time,user_id,user_name )VALUES (now(), old.user_id,old.user_name); INSERT into log_info ( create_time,user_id,user_name )VALUES (now(), new.user_id,new.user_name); END || delimiter;

After the test, execute the trigger immediately after updating the user, you can see the log_info table operation log inserted! The following is a snapshot of the log_info table.

​

  • Test insert to get updated data

    INSERT into user (user_name,user_account) VALUES(‘ zhangsan’,’zhangsan’); Insert into user

    delimiter || create trigger trigger_demo_insert AFTER Insert ON user for each row BEGIN INSERT into log_info ( create_time,user_id,user_name )VALUES (now(), new.user_id,new.user_name); END || delimiter;

​

After the test, the trigger was executed immediately after the new user was added, and the inserted data was successfully written into the log_INFO table. The following is a screenshot of the log_INFO table data.

​

  • Test DELETE to get the updated data

    DELETE FROM user where user_old = ‘999

    delimiter || create trigger trigger_demo_delete AFTER delete ON user for each row BEGIN INSERT into log_info ( create_time,user_id,user_name )VALUES (now(), old.user_id,old.user_name); END || delimiter;

After the test, the trigger was executed immediately after the deletion, and the data before the deletion was successfully written into the log_INFO table. The following is a screenshot of the log_INFO table data.

​

​

2. Look at triggers

And check database (show databases;) View tables (show tables;) Again, the syntax for viewing triggers is as follows:

show triggers; Show create trigger trigger_demo_delete; # view the trigger creation statementCopy the code

​

Delete trigger

Just like deleting a database or table, the syntax for deleting a trigger is as follows: Remember: triggers cannot be modified, only deleted.

Drop trigger + Trigger nameCopy the code

Demo: direct command delete;

DROP trigger trigger_demo_delete; # delete triggerCopy the code

You can also select the trigger to be deleted through Navicat. Click “Delete Trigger” button, and a second confirmation will pop up. Click “Delete”.

​

Q: Can triggers be deleted in batches?

Answer: After testing, can’t.

​

Failed to delete multiple files under verification.

Obviously, batch deletion is not supported.

​

The single deletion succeeds. Procedure

​

Remember: It is important to delete a trigger immediately if it is no longer needed to avoid accidental action. Remember remember!!

#6 Trigger execution order

The database we set up is generally InnoDB database, and the tables set up on it are transactional tables, that is, transaction safe. If the SQL statement or trigger fails to execute, MySQL will roll back the transaction.

(1) If the BEFORE trigger fails to execute, the SQL cannot execute correctly. ② When the SQL fails to execute, the AFTER trigger will not trigger. ③ If the AFTER trigger fails to execute, the SQL will be rolled back.

​

4. MySQL trigger application

Trigger is for each row of records in the database, each row of data before and after the operation will have a corresponding state, trigger will not operate before the state saved in the old keyword, the state after the operation saved in new;

​

Insert, update, and delete cannot be performed in MySQL triggers. Otherwise, an error will be reported.

5. Advantages and disadvantages of MySQL triggers

Advantages of # 1

  • SQL triggers provide an alternative way to check data integrity.
  • SQL triggers can catch errors in the business logic in the database layer.
  • SQL triggers provide another way to run scheduled tasks. By using SQL triggers, you don’t have to wait to run planned tasks because triggers are automatically invoked before or after changes are made to the data in the table.
  • SQL triggers are useful for auditing changes to data in tables.

# 2 faults

  • SQL triggers can only provide extended validation and cannot replace all validation. Some simple validation must be done at the application layer. For example, you can use JavaScript or server-side using server-side scripting languages such as JSP, PHP, ASP.NET, Perl, and so on to validate user input on the client side.
  • Calling and executing SQL triggers from the client application is not visible, so it is difficult to know what is happening in the database layer.
  • SQL triggers can add overhead to the database server.

​

4. Common commands

show triggers; Show create trigger [trigger name] DROP trigger [trigger name]; # delete triggerCopy the code

Personal advice

It is only used in management systems for projects with low concurrency. If it’s a high-concurrency user-facing application, don’t use it. Triggers and stored procedures themselves are difficult to develop and maintain and cannot be migrated efficiently. Triggers can be replaced entirely with transactions. Stored procedures can be replaced by back-end scripts.

​

Six, hot article recommendation

  • What is the experience of developing with IDEA on Ubuntu? What a surprise… Surprise at the end!
  • 520 night I spent 288 ocean to get small teacher younger sister favour, the reason is unexpectedly… Make sure to see it to the end!
  • You’ve never used a code generation tool, but it’s up to you
  • How to implement Springboot project to save local system log files, super detailed, you deserve to have!

If you find this article helpful, please do not forget to put it in the lower left corner of the article,

Direct pia to light it up!!

If I were you, don’t hesitate to go straight to my favorites to eat ashes! No matter later use not on, first eat ash again, ha ha ha ha ha ha ha belch ~~

​

If this article has been helpful, please put your thumb in the bottom left corner of the article. (# ^. ^ #);

If you like the articles shared by Bug bug, please send bug bug a note! The danjun ‘ᴗ, you guys will have a cameo appearance with you.

If you have any questions about this article, please leave a comment below or join the group [Group number: 708072830].

In view of limited personal experience, all views and technical research points, if you have any objection, please directly reply to participate in the discussion (no offensive comments, thank you);

Copyright notice: This article is the blogger’s original article, reprint please attach the original source link and this article statement, all rights reserved, piracy will investigate! (* ^ del ^ *).

​