<>MySQL trigger
Trigger is a special stored procedure , A trigger, like a stored procedure, is a function that can perform a specific function , Stored on the database server SQL fragment , However, the trigger does not need to be called , When executing on the data in the database table DML This is triggered automatically during operation SQL Execution of fragments , No manual call required .
<> stay MySQL in , Just execute insert,update,delete The trigger execution can only be triggered when the operation is <>
This feature of trigger can help the application in the database to ensure the integrity of data , Logging , Data verification and other operations . <>
Use alias OLD and NEW To reference the changed record content in the trigger , This is similar to other databases , Trigger values now support row level triggering , Statement level triggering is not supported .
<> Characteristics of trigger
① What conditions trigger : insert,delete,update ② When is it triggered : Before or after addition, deletion or modification ③ Trigger frequency : Execute for each row ④ Triggers are defined on the table ,
Attach to table
<> Create trigger
-- Create a trigger with only one execution statement format : create trigger Trigger Name before|after Trigger event on Table name for each row
Execute statement ; -- Create a trigger with multiple execution statements format : create trigger Trigger Name before|after Trigger event on Table name for each
row begin Execution statement list end; <>demo -- demand : When user Add a row of data to the table , Will automatically user_log Add logging delimiter
$$ create trigger trigger_test after insert on userfor each row begin insert
into user_logsvalues(1,now,' New users added '); end $$ delimiter;
<> Trigger action -NEW and OLD
<>mysql Defined in new and old, Used to represent the table where the trigger is located , Which row of data triggered the trigger , To reference the changed record content in the trigger , The details are as follows
Trigger type trigger type NEW and OLD Use of
INSERT Type trigger NEW Indicates the data to be added or has been added
UPDATE Type trigger OLD Indicates the data before modification ,NEW Indicates the data to be or has been modified
DELETE Type trigger OLD Indicates the data to be deleted or already deleted <> usage method : NEW.columnName(columnName A column name for the corresponding data table ) <>
demo -- insert delimiter && create trigger test1 after insert on user for each
row begin insert into user_logsvalues(1,now,concat(' New users added , Information is ',NEW.uid,NEW.
username,NEW.password)); end $$ delimiter; -- update delimiter && create
trigger test2 after update on userfor each row begin insert into user_logs
values(1,now,concat(' Modify user , Previous information is ',OLD.uid,OLD.username,OLD.password,' The modified information is ',
NEW.uid,NEW.username,NEW.password)); end $$ delimiter; -- delete delimiter &&
create trigger test3 after delete on userfor each row begin insert into
user_logsvalues(1,now,concat(' Deleted by user , Information is ',OLD.uid,OLD.username,OLD.password));
end $$ delimiter;
<> trigger - see
show triggers;
<> trigger - delete
drop trigger if exists trigger_test;
<> Precautions for trigger
① MYSQL This table cannot be modified in the trigger insert,update,delete operation , Avoid recursive loop triggering ② Minimize the use of triggers , Suppose the trigger triggers each execution 1s,
insert table500 Data bar , Then it needs to be triggered 500 Secondary trigger , Trigger execution alone takes time 500s, and insert 500 The total number of pieces of data is 1s,
that insert The efficiency of is very low . ③ Triggers are for each row , Remember not to use triggers on tables with frequent additions, deletions and changes , Because it will consume resources very much .

©2019-2020 Toolsou All rights reserved,
【C++ Must see for entry 】C++ from 0 reach 1 Introductory programming axios Interceptor packaging and use Spring Boot Interview must ask : Automatic configuration principle VMware 16 install centos 7 Detailed tutorial C Language data structure - Sequence table delete duplicates V2.0.0 The 12th Blue Bridge Cup c++b Group personal problem solving On sending data from serial port single chip microcomputer to upper computer centos7 install RabbitMqjava Polymorphic array of opencv-python Fourier transform and inverse transform