A simple example of a trigger entry in MySQL

Create triggers. The syntax for creating triggers is as follows:

CREATE TRIGGER trigger_name trigger_time trigger_event

On tbl_name for each ROW trigger_stmt

Where the trigger_name identifies the trigger name and is specified by the user;

Trigger_time identification trigger time, with before and after replacement;

Trigger_event identifies the trigger event and replaces it with insert,update and delete;

Tbl_name identifies the name of the table on which the trigger was created, that is, the table on which the trigger was created;

The trigger_stmt is the trigger body; the trigger program can use begin and end as the starting and ending, with more than one statement in the middle;

~ ~ Description ~ ~

CREATE TRIGGER < trigger name >-the trigger must have a name, up to 64 characters, and may be followed by a separator. It is basically like the naming of other objects in MySQL. {before |      After}--the trigger has an execution time setting: It can be set before or after the event occurs. {INSERT | UPDATE |      DELETE}-can also set trigger events: They can be triggered during an insert, update, or delete process. On < table name >--triggers belong to a table: triggers are activated when an INSERT, update, or delete operation is performed on this table.      We cannot schedule two triggers for the same event on the same table.      For each row--the execution interval of a trigger: the For each row clause notifies the trigger to perform an action every other row instead of executing the entire table once.    < trigger SQL Statement >-the trigger contains the SQL statement to be fired: The statement here can be any legitimate statement, including compound statements, but the statements here are subject to the same restrictions as the functions. You must have considerable permissions to create triggers (create TRIGGER), if you are already root, then that's enough. This is different from the SQL standard.

~ ~ Instance ~ ~

Example1:

CREATE TABLE Tab1

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

CREATE TABLE TaB2

DROP TABLE IF EXISTS tab2;

CREATE TABLE TAB2 (

tab2_id varchar (11)

);

Create triggers: T_AFTERINSERT_ON_TAB1 effect: Increase TAB1 table records automatically add records to TAB2 table

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;

Test

INSERT into TAB1 (tab1_id) VALUES (' 0001 ');

Look at the results.

SELECT * from TAB1;

SELECT * from TAB2;

Example2:

Create Trigger: t_afterdelete_on_tab1 function: Delete tab1 table record automatically remove corresponding record in TAB2 table

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;

Test

DELETE from Tab1 WHERE tab1_id= ' 0001 ';

Look at the results.

SELECT * from TAB1;

SELECT * from TAB2;

Sequence of execution of MySQL triggers

A few issues related to triggers to be thrown first

3.1 If the before type of trigger program fails, will SQL execute successfully?

The experiment is as follows:

1 create before triggers 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 Execute SQL:

Update Planinfo set showprob=200 where planid=1; Trigger trigger program;

4 because there is no fc_output.abc,before trigger execution failure, prompt:

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

5) View again:

Mysql> Select Showprob from Planinfo where planid=1;
+----------+
| Showprob |
+----------+
| 2 |
+----------+

That is, the modification of SQL failed to execute successfully. That is, if the before trigger fails, SQL also fails.