Simple MySQL trigger instance

TRIGGER Syntax: CREATETRIGGER & lt; Trigger name & gt; -- the trigger must have a name up to 64 characters, which may be followed by a separator. it is similar to the naming of other objects in MySQL. {BEFORE | AFTER} -- Trigger has execution time setting: it can be set to BEFORE or AFTER an event .... "> <LINKhref =" http: // www. php100

 

Trigger syntax:

CREATE TRIGGER <触发器名称> -- The trigger must have a name and a maximum of 64 characters. it may be followed by a separator. it is similar to the naming method of other objects in MySQL.

{BEFORE | AFTER} -- Trigger has the execution time setting: it can be set to BEFORE or AFTER an event occurs.

{INSERT | UPDATE | DELETE} -- trigger events can also be set: they can be triggered during insert, update, or delete execution.

ON <表名称> -- A trigger belongs to a table. When an insert, update, or delete operation is performed on the table, the trigger is activated. we cannot schedule two triggers for the same event of the same table.

For each row -- trigger execution interval: the for each row clause notifies the trigger to execute an action every ROW, instead of executing an action FOR the entire table.

<触发器sql语句> -- The trigger contains the SQL statement to be triggered: the statement here can be any legal statement, including compound statements, but the statements here are subject to the same restrictions as the functions.

Tip: You must have considerable permissions to CREATE a TRIGGER. if you are a Root user, this is enough. This is different from the SQL standard.

Instance:

Example1:

Create table tab1:

1234	Drop table if exists tab1; create table tab1 (tab1_id varchar (11 ));

Create table tab2:

1234	Drop table if exists tab2; create table tab2 (tab2_id varchar (11 ));

Create trigger: t_afterinsert_on_tab1

Purpose: automatically add a record to the tab2 table after adding a record to the tab1 table

1234567

Drop trigger if exists t_afterinsert_on_tab1; create trigger t_afterinsert_on_tab1AFTER insert on tab1FOR each rowbegin insert into tab2 (tab2_id) values (new. tab1_id); END;

Test:

1	Insert into tab1 (tab1_id) values ('20140901 ');

Let's see the results. it is estimated that both tables have the same data!