Create and delete MySQL triggers

The following article describes how to create a MySQL trigger and how to delete a MySQL trigger. If you create a MySQL trigger correctly, if you are interested in deleting the MySQL trigger, you can click the following article to view it. 1. Create a MySQL trigger. Syntax: CREATETRIGGERtrigger_nametrigger_timetrigger_eve

The following article describes how to create a MySQL trigger and how to delete a MySQL trigger. If you create a MySQL trigger correctly, if you are interested in deleting the MySQL trigger, you can click the following article to view it. 1. Create a MySQL trigger. Syntax: CREATETRIGGERtrigger_nametrigger_timetrigger_eve

The following article describes how to create a MySQL trigger and how to delete a MySQL trigger. If you create a MySQL trigger correctly, if you are interested in deleting the MySQL trigger, you can click the following article to view it.

1. Create a MySQL trigger:

Syntax:

 
 

  
  
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 preceding example is incorrect. Updating the table when it is triggered will bring the program into an endless loop.

The system will report this error: it is already used by statement which invoked this stored function/trigger.

Replace the statement with the following:

 
 

  
  
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 keywords

Trigger_name: name of the trigger. My commonly used naming rules are t_name_tableName _ (B | a) (I | u | d), t: MySQL trigger identifier, name: English name, tableName: table Name, B (BEFORE): BEFORE the event is triggered, a (AFTER): AFTER the event is triggered, I (insert): identifies the insert event, u (update ): identifies an update event. d (delete): identifies a delete event;

Trigger_time: trigger time (BEFORE or AFTER)

Trigger_event: event name (insert, update, or delete)

Tbl_name: Table Name (must be a permanent table)

Trigger_stmt: Execute the statement (which can be a compound language name). Use the OLD and NEW aliases to reference columns in the table related to the trigger program.

2. Delete the producer

Syntax:

 
 

  
  
DROP TRIGGER [schema_name.]trigger_name； 
 
 

Note: The above operations require the SUPER permission.

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 ; 
 
 

The above content is an introduction to the use of MySQL triggers. I hope you can gain some benefits.