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

This article mainly explains the method of using mysql trigger, the content is clear, interested friends can learn, I believe it will be helpful after reading.

What is a trigger: a trigger is used to perform some actions "automatically" after certain actions. (for example, if new student information is inserted, then the number of students should be modified in the class table). When insert delete update sets the trigger, performing the insert delete update operation automatically triggers the contents of the setting. A table can have up to six triggers (3x2, three operations * two times (before | after)). Create trigger: syntax: create trigger trigger name before | after event on table name for each row trigger statement; trigger name is recommended to be trigger_xxx, which is easy to distinguish, and the trigger name cannot be repeated. Before | after represents the trigger statement execution time. In the case of before, the trigger statement is executed before the insert delete update operation; after is after. The event is one of the insert delete update operations. For each row is a trigger that performs the corresponding action on behalf of any record. The trigger statement is the statement to be executed when the trigger is triggered. For example: the following is a new data is inserted into the user creation schedule when a new user is inserted, which is the current time. Create trigger trigger_addUserTime before insert on user_info for each row insert into usercreatetime (create_time) values (now ()); create table user_info (id int primary key auto_increment,username varchar (20) not null,password varchar (20) not null) create table usercreatetime (id int primary key auto_increment,create_time datetime); create trigger trigger_addUserTime before insert on user_info for each row insert into usercreatetime (create_time) values (now ()); insert into user_info (username,password) values ("admin", "admin888"); select * from usercreatetime Multiple trigger statements: multiple statements need to be wrapped in begin end, such as:

Note, however, that the above applies to non-command-line mode (above is run in navicat). If you want to execute on the command line, you need to modify the command Terminator (the mysql command defaults to ";" as the statement Terminator, and if we don't change it, type a trigger statement and use the At the end, the statement is considered to have ended, and then an error is reported because the statement is incomplete.) [delimiter custom symbol-temporarily modifies the statement Terminator to the specified symbol]. [end] delimiter $$--generally defined as $$create trigger trigger name before | after event on table name for each row begin statement; statement; end $$delimiter;-- example delimiter $$--generally defined as $create trigger trigger_addUserTime23 before insert on user_info for each row begin insert into usercreatetime (create_time) values (now ()); insert into usercreatetime (create_time) values (now ()); end $$delimiter

View triggers: use show triggers\ G to view all triggers in the database information_schema by looking at the trigger table to view triggers: use select * from triggers\ G; [of course you can look up data with trigger name as where condition] View trigger creation statement: show create trigger trigger name\ G; delete trigger: use drop trigger trigger name To delete the old and new record references of the trigger: for some cases, you may need to refer to the previous data. For example, to increase the number of students, you need to know that the previous number of students new is the newly inserted data, and the old is the original data [there is no old data when inserted, no new data is deleted, and updates are both old and new. (this refers to the event type)] use odl\ new. The field name refers to the data. (note that the reference is to the data of the statement that triggered the trigger (event). Here is an example: (when inserting a student record, get the name of the newly inserted student through new.name, and then insert new.name into another table) create table student (id int primary key auto_increment,name varchar (15) not null,gender varchar (15) not null); create table stu_info (name varchar (15) not null) Create trigger addCount before insert on student for each row insert into stu_info values (new.name); insert into student (name,gender) values ("lilei", "male"); select * from stu_info; after reading the above content, do you have a better understanding of how to use mysql triggers? if you want to learn more, 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.