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

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

Привіт Хабр! Хочу розповісти в статті мій досвід реалізації бізнес-логіки (БЛ) у MySQL.

Є різні думки щодо питання варто зберігати БЛ в базі.
Я багато років працюю з 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 INSERT BEFORE 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 INSERT BEFORE 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, create_webdir_failed FROM org o WHERE o.id = NEW.org_id_client;
IF NEW.sum > max_limit THEN
CALL raise_error(CONCAT('Сума (', NEW.sum
, ') по клієнту ', create_webdir_failed
, ' не може перевищувати ліміт ', max_limit
, ' в документі з ID = ', NEW.id));
END IF;
END

Або більш красивий варіант з використанням функції
CREATE TRIGGER docs_bef_ins_trg INSERT BEFORE 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 INSERT BEFORE 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) 0 ELSE END
+ CASE WHEN NEW.doc_id = id THEN IFNULL(NEW.kol * NEW.price, 0) 0 ELSE 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, 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 ROW\n');
SET text := CONCAT(text, 'this_proc:BEGIN\n');
SET text := CONCAT(text, 'IF @disable_', table_name, '_trg = 1 THEN\n');
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 INSERT BEFORE 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)
, тип 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", "І", 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", "І", 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)
, тип 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)
, тип 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.6 c
    Але повторюся, це вхолосту. У реальному коді частка витрат на створення та заповнення даних в 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(INT n)
    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.28 c
    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` INSERT BEFORE 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 INSERT BEFORE 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)
    , тип 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", "І", 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", "І", 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.6 c:
    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)
    , тип 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 INSERT BEFORE 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", "І", 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", "І", 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$
    Разом
    Цей інструментарій дозволяє описувати БЛ на рівні БД найменшою кількістю коду, з максимальною продуктивністю і ефективністю.
Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.