Triggers in mysql

A trigger is a stored procedure that is not called directly, but executed when a certain event occurs (insert, delete, update a string).
Trigger support in MySQL started with version 5.0.2

The syntax for creating a trigger:

CREATE TRIGGER trigger_name trigger_time trigger_event
This code was highlighted with the Source Code Highlighter .

trigger_name - the name of the trigger
trigger_time - trigger trigger time. BEFORE - before the event. AFTER - after the event.
trigger_event - Event:
insert - the event is raised by insert, data load, replace operators
update - the event is raised by the update statement
delete - the event is raised by the operators delete, replace. The DROP TABLE and TRUNCATE statements do not activate trigger execution.
tbl_name is the name of the table
trigger_stmt expression that is executed when the trigger is activated

Application

Log

Initial data:

- the table we will follow
CREATE TABLE `test` (
ʻid` INT (11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`content` TEXT NOT NULL
) ENGINE = MYISAM
- log
CREATE TABLE `log` (
ʻid` INT (11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`msg` VARCHAR (255) NOT NULL ,
` time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
`row_id` int (11) NOT NULL
) ENGINE = MYISAM
- trigger
DELIMITER |
CREATE TRIGGER `update_test` AFTER INSERT ON` test`
FOR EACH ROW BEGIN
INSERT INTO log Set msg = 'insert' , row_id = NEW .id;
END ; * This source code was highlighted with Source Code Highlighter .

Now add an entry to the test table. A record will also appear in the log table, pay attention to the row_id field, it contains the id of the row inserted by you.

Extended log:

Initial data:

- Remove trigger
DROP TRIGGER `update_test`;
- Create another table,
- in which backup copies of rows from the test table will be stored
CREATE TABLE `testing`` backup` (
ʻid` INT (11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`row_id` int (11) unsigned NOT NULL ,
`content` TEXT NOT NULL
) ENGINE = MYISAM
- triggers
DELIMITER |
CREATE TRIGGER `update_test` before update ON` test`
FOR EACH ROW BEGIN
INSERT INTO backup Set row_id = OLD .id, content = OLD .content;
END ;

CREATE TRIGGER `delete_test` before delete ON` test`
FOR EACH ROW BEGIN
INSERT INTO backup Set row_id = OLD .id, content = OLD .content;
Source Code Highlighter .

Now if we edit or delete a line from test, it will be copied to backup.

PS: I hope the article was interesting and useful
UPD: Creation of triggers in pre-5.1.6 version requires superuser privileges.