Triggers in MySQL

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

The syntax for creating a trigger is:

CREATE TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt * This source code was highlighted with Source Code Highlighter .

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

Application

Log

Initial data:

- the table we will monitor
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
-
DELIMITER trigger |
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. An entry will also appear in the log table, pay attention to the row_id field, it contains the id of the row you inserted.

Extended log:

Initial data:

- Delete the trigger
DROP TRIGGER `update_test`;
- Let's create another table,
which will store backup copies of rows from the test table
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
-
DELIMITER triggers |
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;
END * This source code was highlighted with 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: to create triggers in versions prior to 5.1.6, superuser privileges are required.