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
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.