Detailed explanation of operation examples of triggers in Mysql

Trigger operation:

1. Trigger creation:

(1). Create a trigger containing a statement

create trigger trigger_name before|after trigger_event on table_name for each row trigger_stmt;

trigger_event represents the operation event ( insert,update,delete);

trigger_stmt represents the executed statement

Example:

create trigger tri_test after insert on t1 for each row insert into t1_log values(USER(),'insert record into t1',now()); 
// 表示的是在对表t1的每一行进行插入操作之后,在表t1_log中插入一条数据,数据是values之后的内容,USER()指的是当前的用户(内置函数),now()当前时间.

(2). Create a trigger containing multiple statements

create trigger trigger_name before|after trigger_event on table_name for each row BEGIN trigger_stmt end;

Example:

delimiter $$
create trigger tri_test
after insert on t1 for each row
BEGIN
insert into t1_log values(USER(),'after insert table t1',now());
insert into t1_log values(USER(),'after insert table t1',now());
END
$$

After execution, execute delimiter; restore the delimiter;

delimiter refers to switching the delimiter, because multiple statements are separated by semicolons, so you need to Switch the default separator, and then switch back after executing the statement.

2. View the trigger:

show triggers /G;
在information_schema数据库中查询
use information_schema;
select * from TRIGGERS where trigger_name = 'tri_test' \G;

3. Modify the trigger

The trigger cannot be modified , you can only delete the original one and then re-create a new trigger.

4. Trigger deletion:

drop trigger trigger_name;

The above is the detailed content of Detailed explanation of operation examples of triggers in Mysql. For more information, please follow other related articles on the PHP Chinese website!