Implementing Business Logic in MySQL

Hi Habr! I want to tell in the article my experience in implementing business logic (BL) in MySQL .

There are different opinions about the question of whether to keep the database in the database. I have been working with Oracle for many years and the Oracle philosophy implies that BL in the database is Best Practices. Let me give you a couple of tom kite quotes:

Tom Kyte. Effective Oracle by Design
If you want it to

Tom Kite. Oracle for professionals.
Before you begin, I would like to explain to you my approach to development. I prefer to solve most problems at the DBMS level. If something can be done in the DBMS, I will do so. [...] My approach is to do everything possible in a DBMS. [...]
When developing database applications, I use a very simple mantra:

if possible, do it with a single SQL statement;
if this cannot be done with a single SQL statement, do it in PL / SQL;
if this cannot be done in PL / SQL, try using a stored procedure in Java;
if this cannot be done in Java, do it in the form of an external C procedure;
if it cannot be implemented as an external procedure in the C language, we need to seriously think about why it should be done at all ...

At the same time, among web-developers, one has to hear opinions that BL in the database is almost an antipattern. But I will not dwell on the question of whether it is worthwhile to implement the BC in the database. Let everyone decide for himself. Those who want to see what I did in the light of the not so extensive (compared to Oracle ) MySQL toolkit, welcome under cat.
')
The implementation assumes a native call to SQL commands (INSERT / UPDATE / DELETE) on the client with a description of the logic in the triggers. All further description will be valid for MySQL 5.1.73 . Here are the main points I encountered while developing:

Row Level Security , see my previous article
Error generation in triggers: unfortunately, you cannot generate an error using the native method in MySQL 5.1.
Convenient writing of logic in triggers: In MySQL, you cannot create 1 trigger on different SQL commands, as a result, the logic will be spread over 6 subroutines
Disable dynamic SQL in triggers
Absence of AFTER STATEMENT TRIGGER: in row-level triggers it is forbidden to change the table where changes are made, in Oracle this problem is solved AFTER by expression-level trigger

Error generation in triggers

When processing a SQL command, it is required to interrupt its execution with an error. For example, if the sum of the document exceeds the limit, then abort the INSERT / UPDATE operation and report an error:

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 $

Searching on the Internet and slightly correcting the decision, such code appeared:

 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 $

And so that in PHP, custom SQL errors are with -20000 code and human error text:

 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; } } }

The final trigger code will look like this:

 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

Or a more beautiful option using the function:

 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

Convenient writing of logic and prohibition of dynamic SQL in triggers

For example, for the positions of the document we need:

check if the document is closed
when inserting a position, if the price is NULL, then determine the price for the client using the get_price function
denormalize the amount of the document in the master table

Here is how it could be written:

 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 $

As a result, we have a lot of code, the code is of the same type, duplicated and spread in 6 places. This code cannot be maintained.

How did I solve this problem? I created triggers that:

in each BEFORE trigger create MEMORY TEMPORARY TABLE with the predefined name <table_name> _tmp_trg with the same columns and the prefixes new_ , old_ and the time and type fields
time field - trigger execution time B - BEFORE, A - AFTER
type field - DML operation, I - INSERT, U - UPDATE, D - DELETE
insert the current values in the trigger NEW. and OLD. in the appropriate fields
The procedure <table_name> _trg_proc is called.
for BEFORE INSERT / UPDATE triggers, read back to the NEW variables . values from the corresponding fields
delete data from temporary table in AFTER trigger DROP TEMPORARY TABLE

Since dynamic SQL in triggers is prohibited, then I wrote a trigger generator.

My Trigger Generator

 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$

This is what code the generator will give us:

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

Trigger Generator Result

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

And here’s a convenient query that checks that the trigger version is the most relevant and after adding the column we did not forget to regenerate the triggers, it can be inserted into the unit tests or even regenerate all the triggers automatically when building the application

Request to check for triggers

 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

What do we get in the end? The single entry point for all changes that make triggers - <table_name> _trg_proc

Now we will rewrite our code for the new system:

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

There is less code, it is all in one place and it is not duplicated! This code is very easy to maintain.

I want to clarify a few points on implementation:

such an approach instead of native triggers, as in the first embodiment, gives some overhead.

On the test data, almost without a “useful” load of 5000 lines, ~ 1.8s is inserted,
in my case 5000 lines ~ 5.9s. If you take out the creation of TEMPORARY TABLE and create
Permanent table and slightly optimize the trigger was able to achieve the result of 5000 for 3.6c.

But again, it is idle. In real code, the share of costs for creating and inserting data in TEMPORARY TABLE will not exceed 20%

Many test queries

 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$

The table should be exactly MEMORY, with non-MEMORY tables, the losses will be quite noticeable. And since table MEMORY, in it we do not process fields of type TEXT.
If you need to disable the trigger, for example, when importing data, you can raise the flag @disable_ <table_name> _trg
```
 SET @disable_test_doc_pos_trg = 1; 
```

Lack of AFTER STATEMENT TRIGGER

The need to change the table at an event in the same table may arise in many cases.

For example, when changing the status (attribute) of a document, you need to create one or a chain of child documents. When changing a branch of nested sets of trees, it is necessary to recalculate the left and right.

I will give an example. The task is, if there is a child document and the child document has a position change, then it is necessary to reduce the amount of relevant material at the main document. Those.There is a Production Plan in which there are many commodity items, when Write-off into production, a document is attached to the Plan and the plan is reduced by the corresponding amount.

Ideally, I would like to write this code:

 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$

But in the trigger it is forbidden to change the same table. I solved this problem like this:

Created a table:

 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`) )

Created a procedure:

 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 @reursive_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 @reursive_sql_sql_text := p_sql_text; PREPARE c_sql FROM @reursive_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$

At the PDO level, after each DML request, I call

 CALL recursive_sql()

Extra calls do not give almost any additional burden.

Here are the tests 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$

Each call is ~ 0.1 ms.

In the trigger, if necessary, change the current table, form the SQL command and insert it into the recursive_sql table. Ie our code will look like this:

 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$

Total

The resulting toolkit allows you to describe BL at the database level with the least amount of code, with maximum performance and efficiency.

Source: https://habr.com/ru/post/312134/

All Articles

Implementing Business Logic in MySQL

Error generation in triggers

Convenient writing of logic and prohibition of dynamic SQL in triggers

Lack of AFTER STATEMENT TRIGGER

Total

More articles: