MySQL Flip-flop syntax explanation

1. Create a MySQL trigger:

Grammar:

CREATE TRIGGER trigger_name trigger_time trigger_event on Tbl_name
For each ROW
BEGIN
TRIGGER_STMT//Statement block
END;

CREATE TRIGGER trigger_name trigger_time trigger_event on tbl_name for each ROW BEGIN trigger_stmt END; uppercase keyword

Trigger_name:mysql trigger Name, I commonly used naming rules t_name_tablename_ (b|a) (i|u|d), T: Trigger ID, Name: English name, TableName: Table name,

B (before): Identity is the trigger event before the

A (after): Identifies the triggering event,

I (INSERT): Identifies the Insert event,

U (UPDATE): Identifies the update event,

D (DELETE): Identifies the delete event;

Trigger_time: Trigger time (before or after)

Trigger_event: Event name (insert or update or delete)

Tbl_name: Table name (must be a permanent table)

TRIGGER_STMT: Executes the statement (which can be a compound name), using the alias old and New, to refer to the columns in the table associated with the trigger.

2. Remove the MySQL hair generator

Grammar:

DROP TRIGGER [schema_name.] Trigger_name;

Note: The above operation requires super privilege

Creation and deletion of MySQL triggers
The following article mainly describes the correct creation of MySQL trigger, the deletion of MySQL trigger, if you are interested in the correct creation of MySQL trigger, the deletion of MySQL trigger, you can click the following article to watch it.

1. Create a MySQL trigger:

Grammar:
CREATE TRIGGER trigger_name trigger_time trigger_event on Tbl_name
For each ROW
BEGIN
Trigger_stmt
END;
CREATE TRIGGER trigger_name trigger_time trigger_event on Tbl_name
For each ROW
BEGIN
Trigger_stmt
END;


Example


CREATE TRIGGER setuserhome after insert on users
For each ROW
BEGIN
Update ' users ' set Homelocationx = 128,
homelocationy=128, homelocationz=30
where uuid = New.uuid
END


The above example is wrong, so that when the table is triggered, the update causes the program to enter a dead loop.


The system will report such an error: It is already used by statement which invoked this stored Function/trigger.

Should be changed to the following statement:
CREATE TRIGGER setuserhome before insert on users
For each ROW
BEGIN
Set New.homelocationx = 128;
Set new.homelocationy = 128;
Set new.homelocationz=30;
END

Uppercase as the keyword


Trigger_name: The name of the trigger, my usual naming convention t_name_tablename_ (B|a) (i|u|d), T:mysql trigger ID, Name: English name, TableName: Table name, B (before): Identity is the triggering event, a (after): Identifies the trigger event, I (insert): Identifies the Insert event, U (UPDATE): Identifies the update event, D (delete): Identifies the delete event;

Trigger_time: Trigger time (before or after)

Trigger_event: Event name (insert or update or delete)

Tbl_name: Table name (must be a permanent table)

TRIGGER_STMT: Executes the statement (which can be a compound name), using the alias old and New, to refer to the columns in the table associated with the trigger.


2. Remove the hair Converter

Grammar:
DROP TRIGGER [schema_name.] Trigger_name;

Note: The above operation requires super privilege


Example:
DROP TRIGGER T_wiley_hotelcomment_bu;
Delimiter//
CREATE TRIGGER T_wiley_hotelcomment_bu before UPDATE on hotel_comment
For each ROW
BEGIN
IF old.ispass=0 && New.ispass=1 Then
UPDATE hotel_info SET sumcommentsumcomment=sumcomment+1,

Sumconsumesumconsume=sumconsume+new.consume,sumservicesumservice=sumservice+new.service,

Sumroomsumroom=sumroom+new.room,sumentironsumentiron=sumentiron+new.entironment,

totaltotal=total+ (new.service+new.room+new.entironment) WHERE hotel_id=new.hotel_id;
ELSEIF Old.ispass=1 && New.ispass=0 Then
UPDATE hotel_info SET sumcommentsumcomment=sumcomment-1,

Sumconsumesumconsume=sumconsume-new.consume,sumservicesumservice=sumservice-new.service,

Sumroomsumroom=sumroom-new.room,sumentironsumentiron=sumentiron-new.entironment,

totaltotal=total-(new.service+new.room+new.entironment) WHERE hotel_id=new.hotel_id;
END IF;
end;//
delimiter;