Blog

MYSQL TRIGGERS

  • 06.07.2016
  • scion

Trigger  is a special type of stored procedure, that the user does not cause directly, it is automatically executed in response to certain events: insert, delete, update data

Supported in MySQL> 5.0.2

Syntax

CREATE TRIGGER name_trigger time_trigger event_trigger

ON name_tbl FOR EACH ROW trigger_expr                 

 

name_trigger - name of the trigger in the table must be unique

time_trigger - starting trigger (BEFORE - before the event, AFTER - after the event)

event_trigger - event

 

/ * Possible events

 

insert - Event caller insert, data load, replace

update - update event is triggered by the operator

delete - Event caller delete, replace

name_tbl - Name of the table

trigger_expr - trigger expression, it is the action that causes SQL- request, and also possible logical expressions

 

Example of usage

 

For example,  after the insertion into the category rows, we need the same data table to be inserted in a left menu. This is often necessary if you want to display the menu entries from multiple tables (modules).

DELIMITER //                                                  
CREATE TRIGGER `insert_left_menu` AFTER INSERT ON `categories`
FOR EACH ROW                                                  
INSERT INTO `left_menu` SET uri=NEW.id, id_category = NEW.id,name = NEW.name,status=NEW.status;
DELIMITER ;                                                   
 

Pay attention to the alias NEW (this means that the field is selected, then it is modified or deleted, there  also can be an alias OLD, which  means the field of action). The idea is the usual sql- request, do not forget to put ";" at the end of the request (not at the end of the trigger, but at the end of request).

But imagine that before you run the query, you need to check the input data. For this you can use a logical expression.

CREATE TRIGGER `insert_left_menu` AFTER INSERT ON `categories`
FOR EACH ROW IF(NEW.id_category IS NULL)                      
THEN                                                          
INSERT INTO `left_menu` SET uri=NEW.id, id_category = NEW.id,name = NEW.name,status=NEW.status;
END IF                                                        
DELIMITER ;

 

IF (NEW.id_category IS NULL) - a logical expression after which it is necessary to do an order  THEN (then) THEN, and only then comes the action.

END IF - the end of a logical expression.

DELIMITER // - change of  divider

It is also possible to deal with variables. Variable declaration.

set @ count = 1;

                                        

Removing the trigger

DROP TRIGGER IF EXISTS `insert_left_menu`;

 

You can not update triggers, you can just delete and create new ones.