Реализация бизнес-логики в MySQL

2016-10-09 в 19:02, admin, рубрики: mysql, oracle, php, sql, бизнес логика

Привет! Хочу рассказать в статье мой опыт реализации бизнес логики (БЛ) в MySQL.
Реализация бизнес-логики в MySQL - 1
Есть разные мнения насчёт вопроса стоит ли хранить БЛ в базе. Я много лет работаю с Oracle и философия Oracle подразумевает, что БЛ в БД это Best Practices. Приведу пару цитат Тома Кайта:

Tom Kyte. Effective Oracle by Design
If the database does something, odds are that it does it better, faster and cheaper, that you could do it yourself

Том Кайт. Oracle для профессионалов.
Прежде чем начать, хотелось бы объяснить вам мой подход к разработке. Я предпочитаю решать большинство проблем на уровне СУБД. Если что-то можно сделать в СУБД, я так и сделаю. [...] Мой подход состоит в том, чтобы делать в СУБД все, что возможно. [...]
При разработке приложений баз данных я использую очень простую мантру:

если можно, сделай это с помощью одного оператора SQL;

если это нельзя сделать с помощью одного оператора SQL, сделай это в PL/SQL;

если это нельзя сделать в PL/SQL, попытайся использовать хранимую процедуру на языке Java;

если это нельзя сделать в Java, сделай это в виде внешней процедуры на языке C;

если это нельзя реализовать в виде внешней процедуры на языке C, надо серьезно подумать, зачем это вообще делать...

В то же время в среде web-разработчиков приходится слышать мнения, что БЛ в БД это чуть ли не антипаттерн. Но я не буду останавливаться на вопросе стоит ли реализовывать БЛ в БД. Пусть каждый решает сам. Тем, кто хочет посмотреть, что у меня получилось в свете не столь обширного (по сравнению с Oracle) инструментария MySQL, добро пожаловать под кат.

Реализация предполагает нативный вызов SQL-команд (INSERT/UPDATE/DELETE) на клиенте с описанием логики в триггерах. Всё дальнейшее описание будет справедливо для MySQL 5.1.73. Вот основные моменты, с которыми я столкнулся при разработке:

Безопасность на уровне строк (Row Level Security), см. мою предыдущую статью
Генерация ошибок в триггерах: увы, нативным методом в MySQL 5.1 ошибку не сгенеришь.
Удобное написание логики в триггерах: В MySQL нельзя создавать 1 триггер на разные SQL-команды, в итоге логика будет размазана по 6 подпрограммам
Запрет динамического SQL в триггерах
Отсутствие AFTER STATEMENT TRIGGER: в триггерах уровня строки запрещено менять таблицу в которую вносятся изменения, в Oracle эта проблема решается AFTER триггером уровня выражения

Генерация ошибок в триггерах

При обработке SQL-команды требуется прервать её выполнение с ошибкой. Например, если сумма документа превышает лимит, то прервать операцию INSERT/UPDATE и сообщить об ошибке:

CREATE TRIGGER docs_bef_ins_trg BEFORE INSERT ON docs FOR EACH ROW
BEGIN
	DECLARE max_limit decimal(10,2);
	SELECT o.max_limit INTO max_limit FROM org o WHERE o.id = NEW.org_id_client;
	IF NEW.sum > max_limit THEN
		-- ???
		-- Тут мы хотим прервать выполнение триггера
		-- и выйти с ошибкой, но в MySQL нет нативных
		-- способов сделать это
		-- ???
	END IF;
END
$

Поискав в интернете и слегка подправив решение, появился такой код:

DELIMITER $
DROP PROCEDURE IF EXISTS raise_error$
CREATE PROCEDURE raise_error(msg TEXT)
BEGIN
  SET @raise_error_msg := IFNULL(msg, '');
  DROP TEMPORARY TABLE IF EXISTS mysql_error_generator;
  CREATE TEMPORARY TABLE mysql_error_generator(raise_error VARCHAR(255) unique) engine=MEMORY;
  INSERT INTO mysql_error_generator VALUES (IFNULL(msg, '')), (IFNULL(msg, ''));
END
$
DROP FUNCTION IF EXISTS raise_error$
CREATE FUNCTION raise_error(msg TEXT) RETURNS TEXT
BEGIN
  CALL raise_error(msg);
  RETURN msg;
END
$

И чтобы в php пользовательские SQL ошибки были с кодом -20000 и человеческим текстом ошибки:

class ExPDOException extends PDOException {
	public function __construct(PDOException $e, PDO $connection) {
		parent::__construct($e->getMessage(), 0, $e->getPrevious());
		$this->code = $e->getCode();
		$this->errorInfo = $e->errorInfo;
		// Пользовательская ошибка
		if ($e->getCode() == 23000 && strstr($e->getMessage(), "for key 'raise_error'")) {
			$this->code = -20000;
			$this->errorInfo[0] = -20000;
			$this->errorInfo[1] = -20000;
			$sql = 'SELECT @raise_error_msg msg';
			$q = $connection->query($sql);
			$msg = $q->fetchColumn();
			$this->message = $msg;
			$this->errorInfo[2] = $msg;
		}
	}
}

Итоговый код триггера будет выглядеть так:

CREATE TRIGGER docs_bef_ins_trg BEFORE INSERT ON docs FOR EACH ROW
BEGIN
	DECLARE max_limit decimal(10,2);
	DECLARE name VARCHAR(255);
	SELECT o.max_limit, o.name INTO max_limit, client_name FROM org o WHERE o.id = NEW.org_id_client;
	IF NEW.sum > max_limit THEN
		CALL raise_error(CONCAT('Сумма (', NEW.sum
			, ') по клиенту ', client_name
			, ' не может превышать лимит ', max_limit
			, ' в документе с ID = ', NEW.id));
	END IF;
END

Или более красивый вариант с использованием функции:

CREATE TRIGGER docs_bef_ins_trg BEFORE INSERT ON docs FOR EACH ROW
BEGIN
	DECLARE msg TEXT;
	SET msg := (SELECT raise_error(CONCAT('Сумма (', NEW.sum
		, ') по клиенту ', o.name
		, ' не может превышать лимит ', max_limit
		, ' в документе с id = ', NEW.id))
		FROM org o
		WHERE o.id = NEW.org_id_client
		 AND NEW.sum > o.max_limit
	);
END

Удобное написание логики и запрет динамического SQL в триггерах

Например, для позиций документа нам необходимо:

проверять, закрыт ли документ
при вставке позиции, если цена NULL, то определить цену по клиенту с помощью функции get_price
денормализовывать сумму документа в мастер таблице

Вот как это могло быть написано:

CREATE TRIGGER doc_pos_bef_ins_trg BEFORE INSERT ON doc_pos FOR EACH ROW
BEGIN
	DECLARE msg TEXT;
	DECLARE org_id_client INT;
	SET msg := (SELECT raise_error(CONCAT('Документ закрыт (id = '
		, d.id, '). Изменения запрещены.'))
		FROM docs d
		WHERE d.id = NEW.doc_id
		 AND d.closed = 1
	);
	IF NEW.price IS NULL THEN
		SELECT d.org_id_client
		INTO org_id_client
		FROM docs d
		WHERE d.id = NEW.doc_id;
		SET NEW.price = get_price(NEW.material_id, org_id_client);
	END IF;
END
$
CREATE TRIGGER doc_pos_bef_upd_trg BEFORE UPDATE ON doc_pos FOR EACH ROW
BEGIN
	DECLARE msg TEXT;
	SET msg := (SELECT raise_error(CONCAT('Документ закрыт (id = '
		, d.id, '). Изменения запрещены.'))
		FROM docs d
		WHERE d.closed = 1 AND d.id IN (OLD.doc_id, NEW.doc_id)
	);
END
$
CREATE TRIGGER doc_pos_aft_del_trg BEFORE DELETE ON doc_pos FOR EACH ROW
BEGIN
	DECLARE msg TEXT;
	SET msg := (SELECT raise_error(CONCAT('Документ закрыт (id = '
		, d.id, '). Изменения запрещены.'))
		FROM docs d
		WHERE d.id = OLD.doc_id
		 AND d.closed = 1
	);
END
$
CREATE TRIGGER doc_pos_aft_ins_trg AFTER INSERT ON doc_pos FOR EACH ROW
BEGIN
	UPDATE docs
	SET sum = IFNULL(sum, 0) + IFNULL(NEW.kol * NEW.price, 0)
	WHERE id = NEW.doc_id;
END
$
CREATE TRIGGER doc_pos_aft_upd_trg AFTER UPDATE ON doc_pos FOR EACH ROW
BEGIN
	UPDATE docs
	SET sum = IFNULL(sum, 0)
	- CASE WHEN OLD.doc_id = id THEN IFNULL(OLD.kol * OLD.price, 0) ELSE 0 END
	+ CASE WHEN NEW.doc_id = id THEN IFNULL(NEW.kol * NEW.price, 0) ELSE 0 END
	WHERE id IN (OLD.doc_id, NEW.doc_id);
END
$
CREATE TRIGGER doc_pos_aft_del_trg AFTER DELETE ON doc_pos FOR EACH ROW
BEGIN
	UPDATE docs
	SET sum = IFNULL(sum, 0) + IFNULL(OLD.kol * OLD.price, 0)
	WHERE id = OLD.doc_id;
END
$

В итоге имеем много кода, код однотипный, дублирующийся и размазанный в 6х местах. Такой код невозможно поддерживать.

Как я решил эту проблему? Я создал триггеры, которые:

в каждом BEFORE триггере создают MEMORY TEMPORARY TABLE с предопределённым именем <table_name>_tmp_trg с одноимёнными столбцами и префиксами new_, old_ и полями time и type
поле time — время выполнения триггера B — BEFORE, A — AFTER
поле type — DML операция, I — INSERT, U — UPDATE, D — DELETE
вставляем текущие значения в триггере NEW. и OLD. в соответствующие поля
вызывается процедура <table_name>_trg_proc
для BEFORE INSERT/UPDATE триггеров считываем обратно в переменные NEW. значения из соответствующих полей
удаляем данные из временной таблицы, в AFTER триггере DROP TEMPORARY TABLE

Т.к. динамический SQL в триггерах запрещён, то я написал генератор триггеров.

Мой генератор триггеров

DELIMITER $
DROP FUNCTION IF EXISTS generate_trigger$
CREATE FUNCTION generate_trigger(table_name VARCHAR(200), trigger_time VARCHAR(200), trigger_type VARCHAR(200)) RETURNS TEXT BEGIN
	DECLARE text TEXT;
	DECLARE trigger_time_short VARCHAR(3);
	DECLARE trigger_type_short VARCHAR(3);
	SET group_concat_max_len = 9000000;
	SET trigger_time_short := LOWER(SUBSTR(trigger_time, 1, 3));
	SET trigger_type_short := LOWER(SUBSTR(trigger_type, 1, 3));
	SET text := '';
	SET text := CONCAT(text, 'DROP TRIGGER IF EXISTS ', table_name, '_', trigger_time_short, '_', trigger_type_short, '_trg$n');
	SET text := CONCAT(text, 'CREATE TRIGGER ', table_name, '_', trigger_time_short, '_', trigger_type_short, '_trg ', trigger_time, ' ', trigger_type, ' ON ', table_name,' FOR EACH ROWn');
	SET text := CONCAT(text, 'this_proc:BEGINn');
	SET text := CONCAT(text, 'IF @disable_', table_name, '_trg = 1 THENn');
	SET text := CONCAT(text, '	LEAVE this_proc;n');
	SET text := CONCAT(text, 'END IF;n');
	IF trigger_time = 'BEFORE' THEN
		-- Создаём временную таблицу
		SET text := CONCAT(text, 'CREATE TEMPORARY TABLE ');
		-- Временная таблица уже может быть создана конструкцией INSERT INTO ... ON DUPLICATE KEY UPDATE поэтому добавляем IF NOT EXISTS
		-- для INSERT IGNORE не сработает AFTER TRIGGER, поэтому тоже добавляем
		IF trigger_type IN ('INSERT', 'UPDATE') THEN
			SET text := CONCAT(text, 'IF NOT EXISTS ');
		END IF;
		SET text := CONCAT(text, table_name, '_tmp_trg (n');
		SET text := CONCAT(text, 'time VARCHAR(1)n');
		SET text := CONCAT(text, ', type VARCHAR(1)n');
		SET text := CONCAT(text, ', col_changed VARCHAR(1000)n, ');
		SET text := CONCAT(text, (SELECT GROUP_CONCAT(CONCAT('new_', COLUMN_NAME, ' ', COLUMN_TYPE, 'n, ', 'old_', COLUMN_NAME, ' ', COLUMN_TYPE) SEPARATOR 'n, ') text
			FROM INFORMATION_SCHEMA.COLUMNS C
			WHERE C.TABLE_NAME = table_name
			AND C.TABLE_SCHEMA = DATABASE()
			AND C.COLUMN_TYPE != 'text'
			));
		SET text := CONCAT(text, ') ENGINE=MEMORY;n');
		-- Создаём переменные
		SET text := CONCAT(text, (SELECT GROUP_CONCAT(CONCAT('SET @new_', COLUMN_NAME, ' := ', IF(trigger_type = 'DELETE', 'NULL', CONCAT('NEW.', COLUMN_NAME)), ';n'
			, 'SET @old_', COLUMN_NAME, ' := ', IF(trigger_type = 'INSERT', 'NULL', CONCAT('OLD.', COLUMN_NAME)), ';') SEPARATOR 'n') text
			FROM INFORMATION_SCHEMA.COLUMNS C
			WHERE C.TABLE_NAME = table_name
			AND C.TABLE_SCHEMA = DATABASE()
			AND C.COLUMN_TYPE != 'text'
			));
		SET text := CONCAT(text, 'n');
	END IF;
	SET text := CONCAT(text, 'INSERT INTO ', table_name, '_tmp_trg VALUES ("', SUBSTR(trigger_time, 1, 1), '", "', SUBSTR(trigger_type, 1, 1), '", ');
	-- заполним col_changed для UPDATE
	IF trigger_type = 'UPDATE' THEN
		SET text := CONCAT(text, 'CONCAT('
			, (SELECT GROUP_CONCAT(CONCAT('IF(IFNULL(NEW.'
				, COLUMN_NAME, ', "-ЪъЪ") != IFNULL(OLD.', COLUMN_NAME, ', "-ЪъЪ"), CONCAT("|', COLUMN_NAME, '|"), "")'
			) SEPARATOR ', ') text
			FROM INFORMATION_SCHEMA.COLUMNS C
			WHERE C.TABLE_NAME = table_name
			AND C.TABLE_SCHEMA = DATABASE()
			AND C.COLUMN_TYPE != 'text'
			), '), ');
	ELSE
		SET text := CONCAT(text, 'NULL, ');
	END IF;
	SET text := CONCAT(text, (SELECT GROUP_CONCAT(CONCAT(
		CASE
			WHEN trigger_time = 'BEFORE' THEN CONCAT('@new_', COLUMN_NAME)
			WHEN trigger_type = 'DELETE' THEN 'NULL'
			ELSE CONCAT('NEW.', COLUMN_NAME)
		END
		, ', '
		, CASE
			WHEN trigger_time = 'BEFORE' THEN CONCAT('@old_', COLUMN_NAME)
			WHEN trigger_type = 'INSERT' THEN 'NULL'
			ELSE CONCAT('OLD.', COLUMN_NAME)
		END
		) SEPARATOR ', ') text
		FROM INFORMATION_SCHEMA.COLUMNS C
		WHERE C.TABLE_NAME = table_name
		AND C.TABLE_SCHEMA = DATABASE()
		AND C.COLUMN_TYPE != 'text'
		));
	SET text := CONCAT(text, ');n');
	SET text := CONCAT(text, 'CALL ', table_name, '_trg_proc;n');
	IF trigger_time = 'BEFORE' THEN
		SET text := CONCAT(text, IF(trigger_type = 'DELETE', '', (SELECT CONCAT('SELECT '
		, GROUP_CONCAT(CONCAT('new_', COLUMN_NAME) SEPARATOR ', ')
		, 'nINTO ', GROUP_CONCAT(CONCAT('@new_', COLUMN_NAME) SEPARATOR ', ')
		, 'nFROM ', table_name, '_tmp_trg;n'
		, CONCAT(GROUP_CONCAT(CONCAT('SET NEW.', COLUMN_NAME, ' := @new_', COLUMN_NAME) SEPARATOR ';n'), ';n')
		) text FROM INFORMATION_SCHEMA.COLUMNS C
		WHERE C.TABLE_NAME = table_name
		AND C.TABLE_SCHEMA = DATABASE()
		AND C.COLUMN_TYPE != 'text'
		)));
		SET text := CONCAT(text, 'DELETE FROM ', table_name, '_tmp_trg;nEND$n');
	ELSE
		SET text := CONCAT(text, 'DROP TEMPORARY TABLE ', table_name, '_tmp_trg;nEND$n');
	END IF;
	RETURN text;
END$

DROP FUNCTION IF EXISTS generate_triggers$
CREATE FUNCTION generate_triggers(p_table_name VARCHAR(200)) RETURNS TEXT BEGIN
	DECLARE table_name VARCHAR(200);
	DECLARE text TEXT;
	SET group_concat_max_len = 9000000;
	SET table_name := p_table_name;
	SET text := '';
	SET text := (SELECT GROUP_CONCAT(generate_trigger(table_name, trigger_time, trigger_type) SEPARATOR 'n')
	FROM (SELECT 'BEFORE' trigger_time
	UNION ALL SELECT 'AFTER' trigger_time) trigger_time
	, (SELECT 'INSERT' trigger_type
	UNION ALL SELECT 'UPDATE' trigger_type
	UNION ALL SELECT 'DELETE' trigger_type
	) trigger_type);
	RETURN text;
END$

Вот какой код нам выдаст генератор:

SHOW CREATE TABLE doc_pos;
SELECT generate_triggers('doc_pos');

Результат генератора триггеров

CREATE TABLE `doc_pos` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `doc_id` int(11) NOT NULL,
  `mat_id` int(11) NOT NULL,
  `kol_orig` decimal(10,3) DEFAULT NULL,
  `kol` decimal(10,3) DEFAULT NULL,
  `price` decimal(17,7) DEFAULT NULL,
  `delivery_date` date DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
  `old_mat_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `doc_id` (`doc_id`,`mat_id`),
  KEY `mat_id` (`mat_id`),
  CONSTRAINT `doc_pos_ibfk_3` FOREIGN KEY (`mat_id`) REFERENCES `materials` (`id`),
  CONSTRAINT `doc_pos_ibfk_1` FOREIGN KEY (`doc_id`) REFERENCES `docs` (`id`),
  CONSTRAINT `doc_pos_ibfk_2` FOREIGN KEY (`mat_id`) REFERENCES `materials` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3137919 DEFAULT CHARSET=utf8 COMMENT='Позиции документов'
$
DROP TRIGGER IF EXISTS doc_pos_bef_ins_trg$
CREATE TRIGGER doc_pos_bef_ins_trg BEFORE INSERT ON doc_pos FOR EACH ROW
this_proc:BEGIN
IF @disable_doc_pos_trg = 1 THEN
	LEAVE this_proc;
END IF;
CREATE TEMPORARY TABLE IF NOT EXISTS doc_pos_tmp_trg (
time VARCHAR(1)
, type VARCHAR(1)
, col_changed VARCHAR(1000)
, new_id int(11)
, old_id int(11)
, new_doc_id int(11)
, old_doc_id int(11)
, new_mat_id int(11)
, old_mat_id int(11)
, new_kol_orig decimal(10,3)
, old_kol_orig decimal(10,3)
, new_kol decimal(10,3)
, old_kol decimal(10,3)
, new_price decimal(17,7)
, old_price decimal(17,7)
, new_delivery_date date
, old_delivery_date date
, new_comment varchar(255)
, old_comment varchar(255)
, new_old_mat_id int(11)
, old_old_mat_id int(11)) ENGINE=MEMORY;
SET @new_id := NEW.id;
SET @old_id := NULL;
SET @new_doc_id := NEW.doc_id;
SET @old_doc_id := NULL;
SET @new_mat_id := NEW.mat_id;
SET @old_mat_id := NULL;
SET @new_kol_orig := NEW.kol_orig;
SET @old_kol_orig := NULL;
SET @new_kol := NEW.kol;
SET @old_kol := NULL;
SET @new_price := NEW.price;
SET @old_price := NULL;
SET @new_delivery_date := NEW.delivery_date;
SET @old_delivery_date := NULL;
SET @new_comment := NEW.comment;
SET @old_comment := NULL;
SET @new_old_mat_id := NEW.old_mat_id;
SET @old_old_mat_id := NULL;
INSERT INTO doc_pos_tmp_trg VALUES ("B", "I", NULL, @new_id, @old_id, @new_doc_id, @old_doc_id, @new_mat_id, @old_mat_id, @new_kol_orig, @old_kol_orig, @new_kol, @old_kol, @new_price, @old_price, @new_delivery_date, @old_delivery_date, @new_comment, @old_comment, @new_old_mat_id, @old_old_mat_id);
CALL doc_pos_trg_proc;
SELECT new_id, new_doc_id, new_mat_id, new_kol_orig, new_kol, new_price, new_delivery_date, new_comment, new_old_mat_id
INTO @new_id, @new_doc_id, @new_mat_id, @new_kol_orig, @new_kol, @new_price, @new_delivery_date, @new_comment, @new_old_mat_id
FROM doc_pos_tmp_trg;
SET NEW.id := @new_id;
SET NEW.doc_id := @new_doc_id;
SET NEW.mat_id := @new_mat_id;
SET NEW.kol_orig := @new_kol_orig;
SET NEW.kol := @new_kol;
SET NEW.price := @new_price;
SET NEW.delivery_date := @new_delivery_date;
SET NEW.comment := @new_comment;
SET NEW.old_mat_id := @new_old_mat_id;
DELETE FROM doc_pos_tmp_trg;
END$

DROP TRIGGER IF EXISTS doc_pos_aft_ins_trg$
CREATE TRIGGER doc_pos_aft_ins_trg AFTER INSERT ON doc_pos FOR EACH ROW
this_proc:BEGIN
IF @disable_doc_pos_trg = 1 THEN
	LEAVE this_proc;
END IF;
INSERT INTO doc_pos_tmp_trg VALUES ("A", "I", NULL, NEW.id, NULL, NEW.doc_id, NULL, NEW.mat_id, NULL, NEW.kol_orig, NULL, NEW.kol, NULL, NEW.price, NULL, NEW.delivery_date, NULL, NEW.comment, NULL, NEW.old_mat_id, NULL);
CALL doc_pos_trg_proc;
DROP TEMPORARY TABLE doc_pos_tmp_trg;
END$

DROP TRIGGER IF EXISTS doc_pos_bef_upd_trg$
CREATE TRIGGER doc_pos_bef_upd_trg BEFORE UPDATE ON doc_pos FOR EACH ROW
this_proc:BEGIN
IF @disable_doc_pos_trg = 1 THEN
	LEAVE this_proc;
END IF;
CREATE TEMPORARY TABLE IF NOT EXISTS doc_pos_tmp_trg (
time VARCHAR(1)
, type VARCHAR(1)
, col_changed VARCHAR(1000)
, new_id int(11)
, old_id int(11)
, new_doc_id int(11)
, old_doc_id int(11)
, new_mat_id int(11)
, old_mat_id int(11)
, new_kol_orig decimal(10,3)
, old_kol_orig decimal(10,3)
, new_kol decimal(10,3)
, old_kol decimal(10,3)
, new_price decimal(17,7)
, old_price decimal(17,7)
, new_delivery_date date
, old_delivery_date date
, new_comment varchar(255)
, old_comment varchar(255)
, new_old_mat_id int(11)
, old_old_mat_id int(11)) ENGINE=MEMORY;
SET @new_id := NEW.id;
SET @old_id := OLD.id;
SET @new_doc_id := NEW.doc_id;
SET @old_doc_id := OLD.doc_id;
SET @new_mat_id := NEW.mat_id;
SET @old_mat_id := OLD.mat_id;
SET @new_kol_orig := NEW.kol_orig;
SET @old_kol_orig := OLD.kol_orig;
SET @new_kol := NEW.kol;
SET @old_kol := OLD.kol;
SET @new_price := NEW.price;
SET @old_price := OLD.price;
SET @new_delivery_date := NEW.delivery_date;
SET @old_delivery_date := OLD.delivery_date;
SET @new_comment := NEW.comment;
SET @old_comment := OLD.comment;
SET @new_old_mat_id := NEW.old_mat_id;
SET @old_old_mat_id := OLD.old_mat_id;
INSERT INTO doc_pos_tmp_trg VALUES ("B", "U", CONCAT(IF(IFNULL(NEW.id, "-ЪъЪ") != IFNULL(OLD.id, "-ЪъЪ"), CONCAT("|id|"), ""), IF(IFNULL(NEW.doc_id, "-ЪъЪ") != IFNULL(OLD.doc_id, "-ЪъЪ"), CONCAT("|doc_id|"), ""), IF(IFNULL(NEW.mat_id, "-ЪъЪ") != IFNULL(OLD.mat_id, "-ЪъЪ"), CONCAT("|mat_id|"), ""), IF(IFNULL(NEW.kol_orig, "-ЪъЪ") != IFNULL(OLD.kol_orig, "-ЪъЪ"), CONCAT("|kol_orig|"), ""), IF(IFNULL(NEW.kol, "-ЪъЪ") != IFNULL(OLD.kol, "-ЪъЪ"), CONCAT("|kol|"), ""), IF(IFNULL(NEW.price, "-ЪъЪ") != IFNULL(OLD.price, "-ЪъЪ"), CONCAT("|price|"), ""), IF(IFNULL(NEW.delivery_date, "-ЪъЪ") != IFNULL(OLD.delivery_date, "-ЪъЪ"), CONCAT("|delivery_date|"), ""), IF(IFNULL(NEW.comment, "-ЪъЪ") != IFNULL(OLD.comment, "-ЪъЪ"), CONCAT("|comment|"), ""), IF(IFNULL(NEW.old_mat_id, "-ЪъЪ") != IFNULL(OLD.old_mat_id, "-ЪъЪ"), CONCAT("|old_mat_id|"), "")), @new_id, @old_id, @new_doc_id, @old_doc_id, @new_mat_id, @old_mat_id, @new_kol_orig, @old_kol_orig, @new_kol, @old_kol, @new_price, @old_price, @new_delivery_date, @old_delivery_date, @new_comment, @old_comment, @new_old_mat_id, @old_old_mat_id);
CALL doc_pos_trg_proc;
SELECT new_id, new_doc_id, new_mat_id, new_kol_orig, new_kol, new_price, new_delivery_date, new_comment, new_old_mat_id
INTO @new_id, @new_doc_id, @new_mat_id, @new_kol_orig, @new_kol, @new_price, @new_delivery_date, @new_comment, @new_old_mat_id
FROM doc_pos_tmp_trg;
SET NEW.id := @new_id;
SET NEW.doc_id := @new_doc_id;
SET NEW.mat_id := @new_mat_id;
SET NEW.kol_orig := @new_kol_orig;
SET NEW.kol := @new_kol;
SET NEW.price := @new_price;
SET NEW.delivery_date := @new_delivery_date;
SET NEW.comment := @new_comment;
SET NEW.old_mat_id := @new_old_mat_id;
DELETE FROM doc_pos_tmp_trg;
END$

DROP TRIGGER IF EXISTS doc_pos_aft_upd_trg$
CREATE TRIGGER doc_pos_aft_upd_trg AFTER UPDATE ON doc_pos FOR EACH ROW
this_proc:BEGIN
IF @disable_doc_pos_trg = 1 THEN
	LEAVE this_proc;
END IF;
INSERT INTO doc_pos_tmp_trg VALUES ("A", "U", CONCAT(IF(IFNULL(NEW.id, "-ЪъЪ") != IFNULL(OLD.id, "-ЪъЪ"), CONCAT("|id|"), ""), IF(IFNULL(NEW.doc_id, "-ЪъЪ") != IFNULL(OLD.doc_id, "-ЪъЪ"), CONCAT("|doc_id|"), ""), IF(IFNULL(NEW.mat_id, "-ЪъЪ") != IFNULL(OLD.mat_id, "-ЪъЪ"), CONCAT("|mat_id|"), ""), IF(IFNULL(NEW.kol_orig, "-ЪъЪ") != IFNULL(OLD.kol_orig, "-ЪъЪ"), CONCAT("|kol_orig|"), ""), IF(IFNULL(NEW.kol, "-ЪъЪ") != IFNULL(OLD.kol, "-ЪъЪ"), CONCAT("|kol|"), ""), IF(IFNULL(NEW.price, "-ЪъЪ") != IFNULL(OLD.price, "-ЪъЪ"), CONCAT("|price|"), ""), IF(IFNULL(NEW.delivery_date, "-ЪъЪ") != IFNULL(OLD.delivery_date, "-ЪъЪ"), CONCAT("|delivery_date|"), ""), IF(IFNULL(NEW.comment, "-ЪъЪ") != IFNULL(OLD.comment, "-ЪъЪ"), CONCAT("|comment|"), ""), IF(IFNULL(NEW.old_mat_id, "-ЪъЪ") != IFNULL(OLD.old_mat_id, "-ЪъЪ"), CONCAT("|old_mat_id|"), "")), NEW.id, OLD.id, NEW.doc_id, OLD.doc_id, NEW.mat_id, OLD.mat_id, NEW.kol_orig, OLD.kol_orig, NEW.kol, OLD.kol, NEW.price, OLD.price, NEW.delivery_date, OLD.delivery_date, NEW.comment, OLD.comment, NEW.old_mat_id, OLD.old_mat_id);
CALL doc_pos_trg_proc;
DROP TEMPORARY TABLE doc_pos_tmp_trg;
END$

DROP TRIGGER IF EXISTS doc_pos_bef_del_trg$
CREATE TRIGGER doc_pos_bef_del_trg BEFORE DELETE ON doc_pos FOR EACH ROW
this_proc:BEGIN
IF @disable_doc_pos_trg = 1 THEN
	LEAVE this_proc;
END IF;
CREATE TEMPORARY TABLE doc_pos_tmp_trg (
time VARCHAR(1)
, type VARCHAR(1)
, col_changed VARCHAR(1000)
, new_id int(11)
, old_id int(11)
, new_doc_id int(11)
, old_doc_id int(11)
, new_mat_id int(11)
, old_mat_id int(11)
, new_kol_orig decimal(10,3)
, old_kol_orig decimal(10,3)
, new_kol decimal(10,3)
, old_kol decimal(10,3)
, new_price decimal(17,7)
, old_price decimal(17,7)
, new_delivery_date date
, old_delivery_date date
, new_comment varchar(255)
, old_comment varchar(255)
, new_old_mat_id int(11)
, old_old_mat_id int(11)) ENGINE=MEMORY;
SET @new_id := NULL;
SET @old_id := OLD.id;
SET @new_doc_id := NULL;
SET @old_doc_id := OLD.doc_id;
SET @new_mat_id := NULL;
SET @old_mat_id := OLD.mat_id;
SET @new_kol_orig := NULL;
SET @old_kol_orig := OLD.kol_orig;
SET @new_kol := NULL;
SET @old_kol := OLD.kol;
SET @new_price := NULL;
SET @old_price := OLD.price;
SET @new_delivery_date := NULL;
SET @old_delivery_date := OLD.delivery_date;
SET @new_comment := NULL;
SET @old_comment := OLD.comment;
SET @new_old_mat_id := NULL;
SET @old_old_mat_id := OLD.old_mat_id;
INSERT INTO doc_pos_tmp_trg VALUES ("B", "D", NULL, @new_id, @old_id, @new_doc_id, @old_doc_id, @new_mat_id, @old_mat_id, @new_kol_orig, @old_kol_orig, @new_kol, @old_kol, @new_price, @old_price, @new_delivery_date, @old_delivery_date, @new_comment, @old_comment, @new_old_mat_id, @old_old_mat_id);
CALL doc_pos_trg_proc;
DELETE FROM doc_pos_tmp_trg;
END$

DROP TRIGGER IF EXISTS doc_pos_aft_del_trg$
CREATE TRIGGER doc_pos_aft_del_trg AFTER DELETE ON doc_pos FOR EACH ROW
this_proc:BEGIN
IF @disable_doc_pos_trg = 1 THEN
	LEAVE this_proc;
END IF;
INSERT INTO doc_pos_tmp_trg VALUES ("A", "D", NULL, NULL, OLD.id, NULL, OLD.doc_id, NULL, OLD.mat_id, NULL, OLD.kol_orig, NULL, OLD.kol, NULL, OLD.price, NULL, OLD.delivery_date, NULL, OLD.comment, NULL, OLD.old_mat_id);
CALL doc_pos_trg_proc;
DROP TEMPORARY TABLE doc_pos_tmp_trg;
END$

И вот удобный запрос, который проверяет, что версия триггера самая актуальная и после добавления столбца мы не забыли перегенерить триггеры, его можно вставить в unit тесты или вообще при сборке приложения автоматом перегенеривать все триггеры

Запрос на проверку триггеров

SELECT DISTINCT CONCAT(EVENT_OBJECT_TABLE, '') msg
FROM (
SELECT EVENT_OBJECT_TABLE
, CONCAT(SUBSTRING_INDEX(gen_trg, 'n', 2), 'n', T.ACTION_STATEMENT, '$', 'n') ACTION_STATEMENT
, gen_trg gen_trg
FROM (
SELECT T.ACTION_STATEMENT ACTION_STATEMENT
, generate_trigger(T.EVENT_OBJECT_TABLE, T.ACTION_TIMING, T.EVENT_MANIPULATION) gen_trg
, T.EVENT_OBJECT_TABLE
FROM INFORMATION_SCHEMA.TRIGGERS T
WHERE T.TRIGGER_SCHEMA = DATABASE()
) T
) T
WHERE T.ACTION_STATEMENT != T.gen_trg

Что в итоге получаем? Единую точку входа для всех изменений, которые делают триггеры — <table_name>_trg_proc

Теперь перепишем наш код под новую систему:

-- Триггер для doc_pos
DROP PROCEDURE IF EXISTS doc_pos_trg_proc$
CREATE PROCEDURE doc_pos_trg_proc()
BEGIN
	DECLARE msg TEXT;
	-- Документ закрыт. Изменения запрещены.
	SET msg := (SELECT raise_error(CONCAT('Документ закрыт (id = '
		, d.id, '). Изменения запрещены.'))
		FROM doc_pos_tmp_trg dp
		INNER JOIN docs d ON d.id IN (dp.new_doc_id, dp.old_doc_id)
		WHERE d.closed = 1 AND dp.time = 'B'
	);
	-- Подставляем цену
	UPDATE doc_pos_tmp_trg
	INNER JOIN docs ON doc_pos_tmp_trg.new_doc_id = docs.id
	SET dp.new_price = get_price(dp.new_material_id, d.org_id_client)
	WHERE dp.time = 'B' AND dp.type = 'I';
	-- Денормализация суммы
	UPDATE docs
	INNER JOIN doc_pos_tmp_trg ON docs.id IN (doc_pos_tmp_trg.new_doc_id, doc_pos_tmp_trg.old_doc_id)
	SET sum = IFNULL(docs.sum, 0)
	- CASE
	    WHEN doc_pos_tmp_trg.old_doc_id = id
	    THEN IFNULL(doc_pos_tmp_trg.old_kol * doc_pos_tmp_trg.old_price, 0)
	    ELSE 0
	  END
	+ CASE
	    WHEN doc_pos_tmp_trg.new_doc_id = id
	    THEN IFNULL(doc_pos_tmp_trg.new_kol * doc_pos_tmp_trg.new_price, 0)
	    ELSE 0
	  END
	WHERE doc_pos_tmp_trg.time = 'A';
END$

Кода стало меньше, он весь в одном месте и он не дублируется! Такой код поддерживать очень легко.

Хочу пояснить несколько моментов по реализации:

такой подход вместо нативных триггеров, как в первом варианте даёт некоторый оверхед.

На тестовых данных, практически без «полезной» нагрузки 5000 строк вставляется ~1.8с,
в моём случае 5000 строк ~5.9с. Если вынести создание TEMPORARY TABLE и создать
перманетную таблицу и слегка оптимизировать триггер удалось достичь результата 5000 за 3.6c.

Но повторюсь, это вхолостую. В реальном коде доля затрат на создание и вставку данных в TEMPORARY TABLE не будет превышать 20%

Много тестовых запросов

DELIMITER $
DROP TABLE IF EXISTS test_doc_pos$
CREATE TABLE test_doc_pos (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `doc_id` int(11) NOT NULL,
  `mat_id` int(11) NOT NULL,
  `kol_orig` decimal(10,3) DEFAULT NULL,
  `kol` decimal(10,3) DEFAULT NULL,
  `price` decimal(17,7) DEFAULT NULL,
  `delivery_date` date DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
  `old_mat_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `doc_id` (`doc_id`,`mat_id`),
  KEY `mat_id` (`mat_id`)
)
$

DROP PROCEDURE IF EXISTS speed_test_doc_pos$
CREATE PROCEDURE speed_test_doc_pos(n INT)
BEGIN
	DECLARE i INT DEFAULT 0;
	WHILE i < n DO
	    INSERT INTO test_doc_pos (doc_id, mat_id, kol, comment) VALUES (i, i, 1, CONCAT('This is comment #', i));
	    SET i := i + 1;
	END WHILE;
END$

-- Запуск без триггеров 5000 - 0.28c
CALL speed_test_doc_pos(5000)$
-- Query OK, 1 row affected (0.28 sec)

-- Вариант 1 с нативными триггерами 5000 - 1.8с:
DROP TRIGGER IF EXISTS test_doc_pos_bef_ins_trg$
CREATE TRIGGER `test_doc_pos_bef_ins_trg` BEFORE INSERT ON `test_doc_pos` FOR EACH ROW
this_proc:BEGIN
    IF @disable_test_doc_pos_trg = 1 THEN
    	LEAVE this_proc;
    END IF;
    SET @db_mode = 'edit';
    SET NEW.price := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE());
END
$
DROP TRIGGER IF EXISTS test_doc_pos_aft_ins_trg$
CREATE TRIGGER `test_doc_pos_aft_ins_trg` AFTER INSERT ON `test_doc_pos` FOR EACH ROW
    this_proc:BEGIN
    IF @disable_test_doc_pos_trg = 1 THEN
    	LEAVE this_proc;
    END IF;
    SET @db_mode = 'show';
END
$

CALL speed_test_doc_pos(5000)$
-- Query OK, 1 row affected (1.88 sec)

-- Вариант 2 - текущая моя версия - 5000 - 5.9с:
DROP PROCEDURE IF EXISTS test_doc_pos_trg_proc$
CREATE PROCEDURE test_doc_pos_trg_proc()
BEGIN
    SET @db_mode = (SELECT IF(time = 'B', 'edit', 'show') FROM test_doc_pos_tmp_trg);
    UPDATE test_doc_pos_tmp_trg SET new_price = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE());
END$

-- SELECT generate_triggers('test_doc_pos')$
DROP TABLE IF EXISTS test_doc_pos_tmp_trg$
DROP TRIGGER IF EXISTS test_doc_pos_bef_ins_trg$
CREATE TRIGGER test_doc_pos_bef_ins_trg BEFORE INSERT ON test_doc_pos FOR EACH ROW
this_proc:BEGIN
IF @disable_test_doc_pos_trg = 1 THEN
	LEAVE this_proc;
END IF;
CREATE TEMPORARY TABLE IF NOT EXISTS test_doc_pos_tmp_trg (
time VARCHAR(1)
, type VARCHAR(1)
, col_changed VARCHAR(1000)
, new_id int(11)
, old_id int(11)
, new_doc_id int(11)
, old_doc_id int(11)
, new_mat_id int(11)
, old_mat_id int(11)
, new_kol_orig decimal(10,3)
, old_kol_orig decimal(10,3)
, new_kol decimal(10,3)
, old_kol decimal(10,3)
, new_price decimal(17,7)
, old_price decimal(17,7)
, new_delivery_date date
, old_delivery_date date
, new_comment varchar(255)
, old_comment varchar(255)
, new_old_mat_id int(11)
, old_old_mat_id int(11)) ENGINE=MEMORY;
SET @new_id := NEW.id;
SET @old_id := NULL;
SET @new_doc_id := NEW.doc_id;
SET @old_doc_id := NULL;
SET @new_mat_id := NEW.mat_id;
SET @old_mat_id := NULL;
SET @new_kol_orig := NEW.kol_orig;
SET @old_kol_orig := NULL;
SET @new_kol := NEW.kol;
SET @old_kol := NULL;
SET @new_price := NEW.price;
SET @old_price := NULL;
SET @new_delivery_date := NEW.delivery_date;
SET @old_delivery_date := NULL;
SET @new_comment := NEW.comment;
SET @old_comment := NULL;
SET @new_old_mat_id := NEW.old_mat_id;
SET @old_old_mat_id := NULL;
INSERT INTO test_doc_pos_tmp_trg VALUES ("B", "I", NULL, @new_id, @old_id, @new_doc_id, @old_doc_id, @new_mat_id, @old_mat_id, @new_kol_orig, @old_kol_orig, @new_kol, @old_kol, @new_price, @old_price, @new_delivery_date, @old_delivery_date, @new_comment, @old_comment, @new_old_mat_id, @old_old_mat_id);
CALL test_doc_pos_trg_proc;
SELECT new_id, new_doc_id, new_mat_id, new_kol_orig, new_kol, new_price, new_delivery_date, new_comment, new_old_mat_id
INTO @new_id, @new_doc_id, @new_mat_id, @new_kol_orig, @new_kol, @new_price, @new_delivery_date, @new_comment, @new_old_mat_id
FROM test_doc_pos_tmp_trg;
SET NEW.id := @new_id;
SET NEW.doc_id := @new_doc_id;
SET NEW.mat_id := @new_mat_id;
SET NEW.kol_orig := @new_kol_orig;
SET NEW.kol := @new_kol;
SET NEW.price := @new_price;
SET NEW.delivery_date := @new_delivery_date;
SET NEW.comment := @new_comment;
SET NEW.old_mat_id := @new_old_mat_id;
DELETE FROM test_doc_pos_tmp_trg;
END$

DROP TRIGGER IF EXISTS test_doc_pos_aft_ins_trg$
CREATE TRIGGER test_doc_pos_aft_ins_trg AFTER INSERT ON test_doc_pos FOR EACH ROW
this_proc:BEGIN
IF @disable_test_doc_pos_trg = 1 THEN
	LEAVE this_proc;
END IF;
INSERT INTO test_doc_pos_tmp_trg VALUES ("A", "I", NULL, NEW.id, NULL, NEW.doc_id, NULL, NEW.mat_id, NULL, NEW.kol_orig, NULL, NEW.kol, NULL, NEW.price, NULL, NEW.delivery_date, NULL, NEW.comment, NULL, NEW.old_mat_id, NULL);
CALL test_doc_pos_trg_proc;
DROP TEMPORARY TABLE test_doc_pos_tmp_trg;
END$

CALL speed_test_doc_pos(5000)$
-- Query OK, 1 row affected (5.91 sec)

-- Вариант 3 - оптимизированная - 5000 - 3.6c:
DROP PROCEDURE IF EXISTS test_doc_pos_trg_proc$
CREATE PROCEDURE test_doc_pos_trg_proc()
BEGIN
    SET @db_mode = (SELECT IF(time = 'B', 'edit', 'show') FROM test_doc_pos_tmp_trg);
    UPDATE test_doc_pos_tmp_trg SET new_price = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE());
END$

SELECT generate_triggers('test_doc_pos')$

DROP TABLE IF EXISTS test_doc_pos_tmp_trg$
CREATE TABLE IF NOT EXISTS test_doc_pos_tmp_trg (
time VARCHAR(1)
, type VARCHAR(1)
, col_changed VARCHAR(1000)
, new_id int(11)
, old_id int(11)
, new_doc_id int(11)
, old_doc_id int(11)
, new_mat_id int(11)
, old_mat_id int(11)
, new_kol_orig decimal(10,3)
, old_kol_orig decimal(10,3)
, new_kol decimal(10,3)
, old_kol decimal(10,3)
, new_price decimal(17,7)
, old_price decimal(17,7)
, new_delivery_date date
, old_delivery_date date
, new_comment varchar(255)
, old_comment varchar(255)
, new_old_mat_id int(11)
, old_old_mat_id int(11)) ENGINE=MEMORY
$

DROP TRIGGER IF EXISTS test_doc_pos_bef_ins_trg$
CREATE TRIGGER test_doc_pos_bef_ins_trg BEFORE INSERT ON test_doc_pos FOR EACH ROW
this_proc:BEGIN
IF @disable_test_doc_pos_trg = 1 THEN
	LEAVE this_proc;
END IF;
DELETE FROM test_doc_pos_tmp_trg;
INSERT INTO test_doc_pos_tmp_trg VALUES ("B", "I", NULL, @new_id, NULL
, NEW.doc_id, NULL
, NEW.mat_id, NULL
, NEW.kol_orig, NULL
, NEW.kol, NULL
, NEW.price, NULL
, NEW.delivery_date, NULL
, NEW.comment, NULL
, NEW.old_mat_id, NULL
);
CALL test_doc_pos_trg_proc;
SELECT new_id, new_doc_id, new_mat_id, new_kol_orig, new_kol, new_price, new_delivery_date, new_comment, new_old_mat_id
INTO @new_id, @new_doc_id, @new_mat_id, @new_kol_orig, @new_kol, @new_price, @new_delivery_date, @new_comment, @new_old_mat_id
FROM test_doc_pos_tmp_trg;
SET NEW.id := @new_id
, NEW.doc_id := @new_doc_id
, NEW.mat_id := @new_mat_id
, NEW.kol_orig := @new_kol_orig
, NEW.kol := @new_kol
, NEW.price := @new_price
, NEW.delivery_date := @new_delivery_date
, NEW.comment := @new_comment
, NEW.old_mat_id := @new_old_mat_id;
DELETE FROM test_doc_pos_tmp_trg;
END$

DROP TRIGGER IF EXISTS test_doc_pos_aft_ins_trg$
CREATE TRIGGER test_doc_pos_aft_ins_trg AFTER INSERT ON test_doc_pos FOR EACH ROW
this_proc:BEGIN
IF @disable_test_doc_pos_trg = 1 THEN
	LEAVE this_proc;
END IF;
INSERT INTO test_doc_pos_tmp_trg VALUES ("A", "I", NULL, NEW.id, NULL, NEW.doc_id, NULL, NEW.mat_id, NULL, NEW.kol_orig, NULL, NEW.kol, NULL, NEW.price, NULL, NEW.delivery_date, NULL, NEW.comment, NULL, NEW.old_mat_id, NULL);
CALL test_doc_pos_trg_proc;
DELETE FROM test_doc_pos_tmp_trg;
-- DROP TEMPORARY TABLE test_doc_pos_tmp_trg;
END$

CALL speed_test_doc_pos(5000)$
-- Query OK, 1 row affected (3.63 sec)

-- Удаляем за собой
DROP TABLE IF EXISTS test_doc_pos$
DROP PROCEDURE IF EXISTS speed_test_doc_pos$

Таблица должна быть именно MEMORY, с не MEMORY таблицами потери будут довольно ощутимыми. И т.к. таблица MEMORY, то в ней мы не обрабатываем поля типа TEXT.
Если необходимо отключить триггер, например, при импорте данных, то можно поднять флаг @disable_<имя_таблицы>_trg
```
SET @disable_test_doc_pos_trg = 1;
```

Отсутствие AFTER STATEMENT TRIGGER

Необходимость изменить таблицу при событии в этой же таблице может возникнуть в многих случаях.

Например, при изменении статуса (атрибута) документа, необходимо создать один или цепочку дочерних документов. При изменении ветки nested sets деревьев, необходимо пересчитать left и right.

Приведу пример. Задача, если есть дочерний документ и у дочернего документа меняется позиция, то необходимо у главного документа уменьшить количество соответствующего материала. Т.е. имеется План производства в котором много товарных позиций, при Списании в производство создаётся документ привязанный к Плану и план уменьшается на соответствующую величину.

В идеале хотелось бы написать такой код:

CREATE PROCEDURE doc_pos_trg_proc()
BEGIN
	-- ...
	UPDATE doc_pos_tmp_trg
	INNER JOIN docs ON docs.id = doc_pos_tmp_trg.doc_id
	INNER JOIN doc_pos ON doc_pos.doc_id = docs.parent_doc_id AND doc_pos.material_id = doc_pos_tmp_trg.material_id
	SET doc_pos.kol = doc_pos.kol - IFNULL(doc_pos_tmp_trg.new_kol, 0) + IFNULL(doc_pos_tmp_trg.old_kol, 0)
	WHERE doc_pos_tmp_trg.time = 'A'
	;
END$

Но в триггере запрещено менять ту же таблицу. Я решил эту проблему так:

Создал таблицу:

CREATE TABLE `recursive_sql` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sql_text` text NOT NULL,
  `pid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `pid` (`pid`)
)

Создал процедуру:

DELIMITER $
DROP PROCEDURE IF EXISTS recursive_sql$
CREATE PROCEDURE recursive_sql()
BEGIN
	DECLARE p_sql_text TEXT;
	DECLARE p_id INT;
	DECLARE p_cn INT;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET @no_data_found = 1;
	SET @no_data_found = NULL;
	cursor_loop: LOOP
		SET @reсursive_sql_sql_text := NULL, p_id := NULL, p_sql_text := NULL;
		SELECT id, sql_text INTO p_id, p_sql_text FROM recursive_sql LIMIT 1 FOR UPDATE;
		IF @no_data_found = 1 OR p_id IS NULL THEN
			LEAVE cursor_loop;
		END IF;
		DELETE FROM recursive_sql WHERE id = p_id;
		SET @reсursive_sql_sql_text := p_sql_text;
		PREPARE c_sql FROM @reсursive_sql_sql_text;
		EXECUTE c_sql;
		DEALLOCATE PREPARE c_sql;
	END LOOP;
	-- Проверим ещё раз
	SELECT COUNT(*) INTO p_cn FROM recursive_sql;
	IF p_cn > 0 THEN
		CALL recursive_sql();
	END IF;
END$

На уровне PDO после каждого DML запроса вызываю

CALL recursive_sql()

Лишние вызовы не дают практически никакой дополнительной нагрузки.

Вот тесты recursive_sql

DELIMITER $
DROP PROCEDURE IF EXISTS recursive_sql_speed_test$
CREATE PROCEDURE recursive_sql_speed_test()
BEGIN
  declare x int unsigned default 0;
  WHILE x <= 100000 DO
    CALL recursive_sql();
    SET x = x + 1;
  END WHILE;
END$
CALL recursive_sql_speed_test()$
-- Query OK, 0 rows affected (9.24 sec)
DROP PROCEDURE IF EXISTS recursive_sql_speed_test$

Каждый вызов ~0.1 мс.

В триггере при необходимости изменить текущую таблицу, формирую SQL-команду и вставляю её в таблицу recursive_sql. Т.е наш код будет выглядеть так:

DROP PROCEDURE IF EXISTS doc_pos_trg_proc$
CREATE PROCEDURE doc_pos_trg_proc()
BEGIN
	-- ...
	INSERT INTO recursive_sql (sql_text)
	SELECT CONCAT('UPDATE doc_pos SET kol = '
	, (doc_pos.kol - IFNULL(doc_pos_tmp_trg.new_kol, 0) + IFNULL(doc_pos_tmp_trg.old_kol, 0))
	, ' WHERE id = ', doc_pos.id) sql_text
	FROM doc_pos_tmp_trg
	INNER JOIN docs ON docs.id = doc_pos_tmp_trg.doc_id
	INNER JOIN doc_pos ON doc_pos.doc_id = docs.parent_doc_id AND doc_pos.material_id = doc_pos_tmp_trg.material_id
	WHERE doc_pos_tmp_trg.time = 'A'
	;
END$

Итого

Получившийся инструментарий позволяет описывать БЛ на уровне БД наименьшим количеством кода, с максимальной производительностью и эффективностью.

Автор: asmm

Источник

Информация

Обсуждаемое

Рекомендуем

Реализация бизнес-логики в MySQL

Генерация ошибок в триггерах

Удобное написание логики и запрет динамического SQL в триггерах

Отсутствие AFTER STATEMENT TRIGGER

Итого

Архив

Информация

Обсуждаемое

Рекомендуем

Реализация бизнес-логики в MySQL

Генерация ошибок в триггерах

Удобное написание логики и запрет динамического SQL в триггерах

Отсутствие AFTER STATEMENT TRIGGER

Итого

Рекомендованный контент

Новости

Актуальные темы

Архив