Shulou(Shulou.com)06/01 Report--

I don't know if you know anything about how to create triggers in MySQL, but today I'm going to tell you a little bit about it. If you are interested, let's take a look at the body. I'm sure you'll get something after reading how to create triggers in MySQL.

1. Background

* trigger is a special stored procedure that triggers execution when inserting, deleting, or modifying data in a particular table. It has finer and more complex data control capabilities than the standard functions of the database itself.

* four elements of trigger creation syntax: 1. Surveillance location (table) 2. Monitoring events (insert/update/delete) 3. Trigger time (after/before) 4. Trigger event

two。 Characteristics of trigger

* Security. The value based on the database gives the user some right to operate the database.

* restrict user actions based on time, such as not allowing database data to be modified after work and holidays.

* restrict the actions of users based on the data in the database, such as not allowing the price of stocks to rise by more than 10% at a time.

* Audit. You can track the user's actions on the database.

* Audit the statements that users operate on the database.

* write the user's updates to the database to the audit table.

* implement complex data integrity rules

* implement non-standard data integrity checks and constraints. Triggers can create more complex restrictions than rules. Unlike rules, triggers can reference columns or database objects. For example, triggers can roll back any futures that attempt to eat more than their own margin.

* provide variable default values.

* implement complex non-standard database-related integrity rules. Triggers can make serial updates to the relevant tables in the database. For example, a delete trigger on the author_code column of the auths table can cause matching rows in other tables to be deleted accordingly.

* cascade modifies or deletes matching rows in other tables when modifying or deleting.

* set the matching rows in other tables to null values when modified or deleted.

* concatenate matching rows in other tables to default values when modified or deleted.

* triggers can reject or roll back changes that undermine the integrity of the relevant changes and cancel transactions that attempt to update data. This trigger works when a foreign key that does not match its primary key is inserted. For example, you can generate an insert trigger on a books.author_code column, and if the new value does not match a value in the auths.author_code column, the insert is rolled back.

* copy the data in the table synchronously and in real time.

* automatically calculate the data value, if the value of the data meets certain requirements, then carry out specific processing. For example, if the amount of money in the company's account is less than 50,000 yuan, send warning data to the financial staff immediately.

3. Trigger example [student score]

* create a score sheet stu

Mysql > CREATE TABLE stu (- > name varchar (64),-> course VARCHAR (32),-> score INT->) ENGINE=INNODB CHARSET=utf8mb4;Query OK, 0 rows affected (0.03 sec)

* create a trigger

When a student's score is less than 0, the score is recorded as 0, and when the score is greater than 100, the score is recorded as 100.

Delimiter set Terminator

Mysql > delimiter / / mysql > CREATE TRIGGER trg_ins_score-> BEFORE INSERT ON stu-> FOR EACH ROW-> BEGIN-> IF NEW.score

SET NEW.score = 0;-> ELSEIF NEW.score > 100 THEN-> SET NEW.score = 100;-> END IF;-> END; / / Query OK, 0 rows affected (0.01 sec) mysql > delimiter

* insert data test

Mysql > INSERT INTO stu SELECT 'tom',' db',-10 political query OK, 1 row affected (0.03 sec) Records: 1 Duplicates: 0 Warnings: 0mysql > INSERT INTO stu SELECT 'tom',' php', 120 political query OK, 1 row affected (0.11 sec) Records: 1 Duplicates: 0 Warnings: 0mysql > select * from stu +-+ | name | course | score | +-+ | tom | db | 0 | tom | php | 100 | +-+ 2 rows in set (0.00 sec)

4. Trigger summary

* flip-flops are costly to performance and should be used with great caution.

* for transaction tables, the entire statement is rolled back if the trigger fails.

* Master-slave copy in Row format, triggers will not be executed on the slave library.

* prevent recursive execution when using triggers.

After reading the article on how to create triggers in MySQL, what do you think? If you want to know more about it, you can continue to follow our industry information section.

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.