Simple example and introduction of triggers in MySQL

Create the trigger. Create trigger syntax as follows:

CREATE TRIGGER trigger_name trigger_time trigger_event

ON tbl_name FOR EACH ROW trigger_stmt

Where trigger_name identifies the trigger name, which is specified by the user;

trigger_time marks the trigger time, replaced by before and after;

trigger_event identity trigger event, replace with insert, update and delete;

tbl_name identifies the table name on which the trigger was created, that is, on which table the trigger was created.

trigger_stmt is the body of the trigger program; The trigger program can start and end with begin and end, with multiple statements in between.

~ ~ ~ ~

CREATE TRIGGER < Trigger name > {BEFORE | AFTER} -- the trigger must have a name, up to 64 characters, possibly followed by a delimiter. It is named in much the same way as other objects in MySQL. {INSERT | UPDATE | DELETE} -- trigger events can also be set: they can be triggered during the execution of insert, update, or delete. ON < The name of the table > Triggers belong to a table: when an insert, update, or delete is performed on this table, the trigger is activated. We cannot assign two triggers to the same event in the same table. FOR EACH ROW -- execution interval of triggers: FOR EACH ROW clause notifies triggers to perform an action every other row, instead of once for the entire table. < Trigger SQL statement > The trigger contains the SQL statement to be fired: the statement here can be any valid statement, including a compound statement, but the statement here is limited to the same thing as the function. You must have considerable permissions to create triggers (CREATE TRIGGER), and if you are already an Root user, that is enough. This is different from the standard for SQL.

~ ~ ~ ~ instance

example1:

Create a table tab1

DROP TABLE IF EXISTS tab1;
CREATE TABLE tab1(
    tab1_id varchar(11)
);

Create a table tab2

 DROP TABLE IF EXISTS tab2; 
 CREATE TABLE tab2( 
    tab2_id varchar(11) 
 );

Create trigger: t_afterinsert_on_tab1

Effect: add the record to tab1 and automatically add the record to tab2

DROP TRIGGER IF EXISTS t_afterinsert_on_tab1; 
 CREATE TRIGGER t_afterinsert_on_tab1 
 AFTER INSERT ON tab1 
 FOR EACH ROW 
BEGIN
    insert into tab2(tab2_id) values(new.tab1_id); 
 END;

Under the test 1

 INSERT INTO tab1(tab1_id) values('0001');

Look at the results

SELECT * FROM tab1;

SELECT * FROM tab2;

example2:

Create trigger: t_afterdelete_on_tab1

Effect: after deleting the records in tab1 table, the corresponding records in tab2 table will be deleted automatically

DROP TRIGGER IF EXISTS t_afterdelete_on_tab1; 
 CREATE TRIGGER t_afterdelete_on_tab1 
 AFTER DELETE ON tab1 
 FOR EACH ROW 
 BEGIN
      delete from tab2 where tab2_id=old.tab1_id; 
END;

Under the test 1

DELETE FROM tab1 WHERE tab1_id='0001';

Look at the results

SELECT * FROM tab1;

SELECT * FROM tab2;

Order of execution of Mysql triggers

First, throw out a few questions about triggers

3.1 if the trigger program of type before fails to execute, will sql execute successfully?

The experiment is as follows:

1) create before trigger in FC_Word.planinfo:

DELIMITER |
create trigger trigger_before_planinfo_update
before update
ON FC_Word.planinfo FOR EACH ROW
BEGIN
insert into FC_Output.abc (planid) values (New.planid);
END

2) view: mysql > select showprob from planinfo where planid=1;

+----------+
| showprob |
+----------+
| 2 |
+----------+

3) sql:

update planinfo set showprob=200 where planid=1; Trigger trigger program;

4) because FC_Output.abc, before trigger failed to execute,

ERROR 1146 (42S02): Table 'FC_Output.abc' doesn't exist

5) check again:

mysql > select showprob from planinfo where planid=1;
+----------+
| showprob |
+----------+
| 2 |
+----------+

That is, the modification sql was not executed successfully. That is, if the before trigger fails to execute, sql will also fail to execute.