Create and delete MySQL triggers _ MySQL

Create and delete a MySQL trigger bitsCN.com

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:

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_comment comment_bu before update on comment _comment

FOR EACH ROW

BEGIN

If old. ispass = 0 & NEW. ispass = 1 THEN

UPDATE into _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 condition _id = NEW. condition _id;

Elseif old. ispass = 1 & NEW. ispass = 0 THEN

UPDATE into _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 condition _id = NEW. condition _id;

End if;

END ;//

Delimiter;

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

BitsCN.com