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

Most people do not understand the knowledge points of this "MySQL trigger then use" article, so the editor summarizes the following content, detailed content, clear steps, and has a certain reference value. I hope you can get something after reading this article. Let's take a look at this "MySQL trigger then use" article.

Flip-flop I. introduction of trigger

Triggers are special stored procedures. Triggers and stored procedures are similar to those that can perform specific functions and store

The SQL segment on the database server, but the trigger needs to be tuned when performing DML operations on the data in the data table

The action triggers the execution of this SQL segment, which needs to be adjusted.

In MySQL, trigger execution can only be triggered by holding the insert\ delete\ update operation.

Second, flip-flops use 1. Create trigger create trigger tri_name-- tri_name: trigger alias-- define trigger time-- define DML type ON-- Table for each to trigger -- for each row declared as a sql-level trigger (trigger executes for each record of an operation)-- for each statement statement-level trigger (trigger agent executes once) begin-- when there is only one sql statement Begin...end can omit sql_statement-- operate end of the DML type corresponding to the trigger # example-create trigger: when you send an add operation to the learning information table, record the entry create trigger tri_test1after insert on studentsfor each rowinsert into stulogs (time,log_text) values (now (), concat ('add', NEW.stu_num,' learning information') in the blog information table); 2. View trigger # View all triggers show triggers;# view the creation statement of the trigger show create trigger trigger name; 3. Delete trigger

Triggers cannot be modified and can only be deleted

Name of drop trigger trigger; III. NEW and OLD

Triggers listen for insert, delete, and update operations on data in a data table, and are usually handled in triggers

Associate some DML operations; we can get the trigger NEW and OLD keywords in the trigger to trigger the trigger.

Data for DML operations of

NEW: the data added by the insert operation and the modified record after the update operation are obtained in the trigger

OLD: the data before the deletion of delete operation and the data before modification of update operation are obtained in trigger.

In the use of 1.NEW # insert operation: NEW indicates the added new record create trigger tri_test1after insert on studentsfor each rowinsert into stulogs (time,log_text) values (now (), concat ('add', NEW.stu_num,' learning information') # in update operation: NEW indicates modified data-create trigger: in the trigger that listens to update operation, you can enable NEW to obtain the modified data create trigger tri_test2after update on students for each rowinsert into stulogs (time,log_text) values (now (), concat ('modify learning information is:', NEW.stu_num,NEW.stu_name) During the # delete operation of 2.OLD: OLD indicates the deleted record create trigger tri_test3after delete on students for each rowinsert into stulogs (time,log_text) values (now (), concat ('delete', OLD.stu_num,' learning information) # update operation: OLD indicates the record before modification create trigger tri_test2after update on students for each rowinsert into stulogs (time,log_text) values (now (), concat ('change the name of the student from [', OLD.stu_name,'] to [', NEW.stu_name,']'). Advantages

The trigger is active and executes when the DML operation of the response is performed on the table related to the trigger.

Triggers can realize the cascading operation (association operation) of the data in the table, which is helpful to ensure the integrity of the data.

Triggers can perform more complex validity checks on the data of DML operations

two。 Shortcoming

If there is a problem with the business logic implemented by the trigger, it will be difficult to locate and maintain later.

The quantity makes the trigger easy to lead to the clutter of the code structure, which increases the complexity of the program.

When the amount of data operated by the trigger is relatively low, the efficiency of the trigger operation will decrease.

3. Make a suggestion

In interconnect items, adaptive triggers should be avoided

For items with high concurrency, you can choose to make the stored procedure, but make the stored procedure is not recommended in the interconnection reference.

(reason: the logic of implementing the business is handed over to the database for processing during the stored procedure, and the load on the database is increased or decreased, which is not conducive to the migration of the database.)

The above is about the content of this article "MySQL trigger and then use". I believe we all have a certain understanding. I hope the content shared by the editor will be helpful to you. If you want to know more about the relevant knowledge, 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.