Mysql raise error

In this tutorial, you will learn how to use MySQL SIGNAL and RESIGNAL statements to raise error conditions inside stored procedures.

Summary: in this tutorial, you will learn how to use SIGNAL  and RESIGNAL statements to raise error conditions inside stored procedures.

MySQL SIGNAL statement

You use the SIGNAL statement to return an error or warning condition to the caller from a stored program e.g., stored procedure, stored function, trigger or event. The SIGNAL  statement provides you with control over which information for returning such as value and messageSQLSTATE.

The following illustrates syntax of the SIGNAL statement:

SIGNAL SQLSTATE | condition_name;
SET condition_information_item_name_1 = value_1,
    condition_information_item_name_1 = value_2, etc;
Code language: SQL (Structured Query Language) (sql)

Following the SIGNAL keyword is a SQLSTATE value or a condition name declared by the  DECLARE CONDITION statement. Notice that the SIGNAL statement must always specify a SQLSTATE value or a named condition that defined with an  SQLSTATE value.

To provide the caller with information, you use the SET clause. If you want to return multiple condition information item names with values, you need to separate each name/value pair by a comma.

The  condition_information_item_name can be MESSAGE_TEXT, MYSQL_ERRORNO, CURSOR_NAME , etc.

The following stored procedure adds an order line item into an existing sales order. It issues an error message if the order number does not exist.

DELIMITER $$

CREATE PROCEDURE AddOrderItem(
		         in orderNo int,
			 in productCode varchar(45),
			 in qty int, 
                         in price double, 
                         in lineNo int )
BEGIN
	DECLARE C INT;

	SELECT COUNT(orderNumber) INTO C
	FROM orders 
	WHERE orderNumber = orderNo;

	-- check if orderNumber exists
	IF(C != 1) THEN 
		SIGNAL SQLSTATE '45000'
			SET MESSAGE_TEXT = 'Order No not found in orders table';
	END IF;
	-- more code below
	-- ...
END
Code language: SQL (Structured Query Language) (sql)

First, it counts the orders with the input order number that we pass to the stored procedure.

Second, if the number of order is not 1, it raises an error with  SQLSTATE 45000 along with an error message saying that order number does not exist in the orders table.

Notice that 45000 is a generic SQLSTATE value that illustrates an unhandled user-defined exception.

If we call the stored procedure  AddOrderItem() and pass a nonexistent order number, we will get an error message.

CALL AddOrderItem(10,'S10_1678',1,95.7,1);





  



                       
  




Code language: JavaScript (javascript)

MySQL SIGNAL example

MySQL RESIGNAL statement

Besides the SIGNAL  statement, MySQL also provides the RESIGNAL  statement used to raise a warning or error condition.

The RESIGNAL  statement is similar to SIGNAL  statement in term of functionality and syntax, except that:

  • You must use the RESIGNAL  statement within an error or warning handler, otherwise, you will get an error message saying that “RESIGNAL when the handler is not active”. Notice that you can use SIGNAL  statement anywhere inside a stored procedure.
  • You can omit all attributes of the RESIGNAL statement, even the SQLSTATE value.

If you use the RESIGNAL statement alone, all attributes are the same as the ones passed to the condition handler.

The following stored procedure changes the error message before issuing it to the caller.

DELIMITER $$

CREATE PROCEDURE Divide(IN numerator INT, IN denominator INT, OUT result double)
BEGIN
	DECLARE division_by_zero CONDITION FOR SQLSTATE '22012';

	DECLARE CONTINUE HANDLER FOR division_by_zero 
	RESIGNAL SET MESSAGE_TEXT = 'Division by zero / Denominator cannot be zero';
	-- 
	IF denominator = 0 THEN
		SIGNAL division_by_zero;
	ELSE
		SET result := numerator / denominator;
	END IF;
END
Code language: SQL (Structured Query Language) (sql)

Let’s call the  Divide() stored procedure.

CALL Divide(10,0,@result);
Code language: CSS (css)

mysql resignal

In this tutorial, we have shown you how to raise error conditions inside stored programs using SIGNAL  and  RESIGNAL statements.

Was this tutorial helpful?

Источник

In this tutorial, we will learn how to raise an error using the signal statement. The SIGNAL and RESIGNAL statements are very similar to each other except few points. We will see the details and examples of the SIGNAL statement in this article and for the RESIGNAL statement, we will have a separate tutorial.

Also read: MySQL Index Hints- FORCE INDEX

Introduction to MySQL SIGNAL

The SIGNAL statement is used to throw an error in the stored programs such as stored procedures, stored programs, triggers or events. Using the SIGNAL, you can return an error number, SQL STATE value or a custom message.

To execute the SIGNAL statement, you don’t need any special privilege.

In short, you can use the SIGNAL statement to return a custom message when an error occurs in the program.

Also read: Variables in MySQL Stored Procedures – Easy Implementation

MySQL SIGNAL Statement Syntax

The following syntax shows the correct way to use the SIGNAL statement-

SIGNAL condition_value
    [SET signal_information_item
    [, signal_information_item] ...]

condition_value: {
    SQLSTATE [VALUE] sqlstate_value
  | condition_name
}

signal_information_item:
    condition_information_item_name = simple_value_specification
Code language: SQL (Structured Query Language) (sql)

Here,

The condition_value tells the error value to be thrown. The condtion_value can be an SQLSTATE or a condition_name which refers to the previously created named definition with the DECLARE … CONDITION statement.

The SQLSTATE value for a SIGNAL statement shouldn’t begin with the number “00” because these values imply success rather than an error. Instead, they should begin with a value other than ’00’. Either the SQLSTATE value is explicitly provided in the SIGNAL statement or it is referenced by a named condition in the statement, but either way, it is true. A Bad SQLSTATE problem manifests itself when the value is incorrect.

Optionally, the SIGNAL statement includes a SET clause with a list of condition information item name = simple_value_specification assignments, separated by commas, for each signal item.

Use the code “45000,” which stands for “unhandled user-defined exception,” to indicate a generic SQLSTATE value.

MySQL SIGNAL Statement Examples

Now we will take some examples to demonstrate the use of the SIGNAL statement.

Here, we will create a procedure to show the use of warnings and errors with the signal statement.

DROP PROCEDURE IF EXISTS signalDemo;
DELIMITER //
CREATE PROCEDURE signalDemo(num INT)
BEGIN
IF num = 0 THEN
  SIGNAL SQLSTATE '01000';
ELSEIF num = 1 THEN
  SIGNAL SQLSTATE '45000'
  SET MESSAGE_TEXT="error raised 1";
ELSEIF num = 2 THEN
  SIGNAL SQLSTATE '45000'
  SET MESSAGE_TEXT="error raised 2";
ELSE 
  SIGNAL SQLSTATE '01000'
  SET MESSAGE_TEXT="warning raised" , MYSQL_ERRNO = 1000;
END IF;
END;
//
DELIMITER ;

Code language: SQL (Structured Query Language) (sql)

Here, if the value of num is 0, the procedure will signal a warning because the SQLSTATE value that starts with ’01’ is the warning.

Note that, the warning doesn’t terminate the procedure.

When the value of num is 1 or 2, the procedure signals an error and sets the message_text item. Here, after the error is caught, the procedure terminates and the text that you set earlier will be displayed.

If the value of num is anything else, the procedure signals the warning and sets the custom message with the error number.

Let’s examine everything by calling the procedures.

CALL signalDemo(0);
Code language: SQL (Structured Query Language) (sql)

Generate Warning With Signal

Generate Warning With Signal
CALL signalDemo(1);
CALL signalDemo(2);
Code language: SQL (Structured Query Language) (sql)

Generate Error With Signal

Generate Error With Signal
CALL signalDemo(3);
Code language: SQL (Structured Query Language) (sql)

Set Custom Warning Message

Set Custom Warning Message

A specific SQLSTATE value is used to signal the condition if the SIGNAL statement specifies that value.

Let’s take an example of divide by zero error.

DROP PROCEDURE IF EXISTS div_by_zero;
DELIMITER //
CREATE PROCEDURE div_by_zero(divisor INT)
BEGIN
IF divisor = 0 THEN
SIGNAL SQLSTATE '22012';
END IF;
END;
//
DELIMITER ;
Code language: SQL (Structured Query Language) (sql)

Divide By Zero Error

Divide By Zero Error

You can also give them a name to the signal statement. If you do so, the condition must be defined using the SQLSTATE value instead of the MySQL error number.

Let’s re-write the above procedure using the named signal statement.

DROP PROCEDURE IF EXISTS div_by_zero;
DELIMITER //
CREATE PROCEDURE div_by_zero(divisor INT)
BEGIN
DECLARE div_by_zero_error CONDITION FOR SQLSTATE '22012';
IF divisor = 0 THEN
SIGNAL div_by_zero_error;
END IF;
END;
//
DELIMITER ;
Code language: SQL (Structured Query Language) (sql)

Named Signal Divide By Zero Error

Named Signal Divide By Zero Error

Summary

In this tutorial, we have learned about the SIGNAL statement in MySQL. It is a broad topic to learn and understand. However, it is one of the most important topics in MySQL. It is absolutely worth learning the SIGNAL topic. If you want to get a more detailed view of the SIGNAL, you can check out its official documentation.

References

MySQL official documentation on the SIGNAL.

Источник

The SIGNAL statements are used as a way to return an error message appearing during code execution. These statements provide error information to an error handler, outer portion of the application or to the client. It returns information of error number, SQLSTATE, value and message that occurs in stored procedures. The syntax of the SIGNAL statement is:

Syntax

SIGNAL SQLSTATE | condition_name;

Where condition_name is the set using SET statements. The multiple signal items declared as comma separated, name/value pairs are optional:

SET condition_1 = value_1,

   condition _2 = value_2, etc;

The ‘condition_1’, ‘condition_2’ … etc, can be any if these: CLASS_ORIGIN, MESSAGE_TEXT, MYSQL_ERRNO, TABLE_NAME, COLUMN_NAME, … The SQLSTATE or condition_name in the SIGNAL indicates the error value that is to be returned. To signal a generic SQLSTATE value, use ‘45000‘, which means «unhandled user-defined exception.»

The example below shows the SIGNAL implementation. The procedure takes the temperature as input, it checks in the database for the status of the machines working in that temperature. If the status returned is 0, it indicates that the machine is not working and the message is sent to the outer function using SIGNAL statements. If the machine status if OK, the details of the machine is displayed.

DELIMITER $$ 

CREATE PROCEDURE settemp(IN temp INT)

BEGIN

   DECLARE T INT; 

   SELECT status INTO T

   FROM tblmachine 

   WHERE temperature = temp;

   IF(T = 0) THEN 

      SIGNAL SQLSTATE '45000'

      SET MESSAGE_TEXT = 'The machine not working';

   ELSE

      SELECT * FROM tblmachine WHERE temperature = temp;

   END IF;

END $$

DELIMITER ;

CALL settemp(35);

The output from above given example is: ‘#1644 — The machine not working’.

The RESIGNAL statement is the same as the SIGNAL one except that the RESIGNAL statement should be used within the error or the warning handler themselves. If the RESIGNAL statement is used outside, the MySQL generates an error saying RESIGNAL when handler is not active. The RESIGNAL statement can be used without any attributes or with attributes as in SIGNAL statement. The above example can be used with RESIGNAL instead, as seen below:

DELIMITER $$

CREATE PROCEDURE settemp(IN temp INT)

BEGIN 

   DECLARE tempexceed CONDITION FOR SQLSTATE '45000';

   DECLARE T INT; 

   DECLARE CONTINUE HANDLER FOR tempexceed

   BEGIN

      RESIGNAL SET MESSAGE_TEXT = 'The machine not working';

   END;

   SELECT status INTO T

   FROM tblmachine

   WHERE temperature = temp; 

   IF(T < 35) THEN

      SIGNAL tempexceed;

   ELSE

      SELECT * FROM tblmachine WHERE temperature = temp;

   END IF;  

END; $$

DELIMITER ;

CALL settemp(12);

The output from above given example is: ‘#1644 — The machine not working’.

Источник

Syntax

SIGNAL error_condition
    [SET error_property
    [, error_property] ...]

error_condition:
    SQLSTATE [VALUE] 'sqlstate_value'
  | condition_name

error_property:
    error_property_name = <error_property_value>

error_property_name:
    CLASS_ORIGIN
  | SUBCLASS_ORIGIN
  | MESSAGE_TEXT
  | MYSQL_ERRNO
  | CONSTRAINT_CATALOG
  | CONSTRAINT_SCHEMA
  | CONSTRAINT_NAME
  | CATALOG_NAME
  | SCHEMA_NAME
  | TABLE_NAME
  | COLUMN_NAME
  | CURSOR_NAME

Contents

  1. Syntax
  2. Errors
  3. Examples
  4. See Also

SIGNAL empties the diagnostics area and produces a custom error. This statement can be used anywhere, but is generally useful when used inside a stored program. When the error is produced, it can be caught by a HANDLER. If not, the current stored program, or the current statement, will terminate with the specified error.

Sometimes an error HANDLER just needs to SIGNAL the same error it received, optionally with some changes. Usually the RESIGNAL statement is the most convenient way to do this.

error_condition can be an SQLSTATE value or a named error condition defined via DECLARE CONDITION. SQLSTATE must be a constant string consisting of five characters. These codes are standard to ODBC and ANSI SQL. For customized errors, the recommended SQLSTATE is ‘45000’. For a list of SQLSTATE values used by MariaDB, see the MariaDB Error Codes page. The SQLSTATE can be read via the API method mysql_sqlstate( ).

To specify error properties user-defined variables and local variables can be used, as well as character set conversions (but you can’t set a collation).

The error properties, their type and their default values are explained in the diagnostics area page.

Errors

If the SQLSTATE is not valid, the following error like this will be produced:

ERROR 1407 (42000): Bad SQLSTATE: '123456'

If a property is specified more than once, an error like this will be produced:

ERROR 1641 (42000): Duplicate condition information item 'MESSAGE_TEXT'

If you specify a condition name which is not declared, an error like this will be produced:

ERROR 1319 (42000): Undefined CONDITION: cond_name

If MYSQL_ERRNO is out of range, you will get an error like this:

ERROR 1231 (42000): Variable 'MYSQL_ERRNO' can't be set to the value of '0'

Examples

Here’s what happens if SIGNAL is used in the client to generate errors:

SIGNAL SQLSTATE '01000';
Query OK, 0 rows affected, 1 warning (0.00 sec)

SHOW WARNINGS;

+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1642 | Unhandled user-defined warning condition |
+---------+------+------------------------------------------+
1 row in set (0.06 sec)

SIGNAL SQLSTATE '02000';
ERROR 1643 (02000): Unhandled user-defined not found condition

How to specify MYSQL_ERRNO and MESSAGE_TEXT properties:

SIGNAL SQLSTATE '45000' SET MYSQL_ERRNO=30001, MESSAGE_TEXT='H
ello, world!';

ERROR 30001 (45000): Hello, world!

The following code shows how to use user variables, local variables and character set conversion with SIGNAL:

CREATE PROCEDURE test_error(x INT)
BEGIN
   DECLARE errno SMALLINT UNSIGNED DEFAULT 31001;
   SET @errmsg = 'Hello, world!';
   IF x = 1 THEN
      SIGNAL SQLSTATE '45000' SET
      MYSQL_ERRNO = errno,
      MESSAGE_TEXT = @errmsg;
   ELSE
      SIGNAL SQLSTATE '45000' SET
      MYSQL_ERRNO = errno,
      MESSAGE_TEXT = _utf8'Hello, world!';
   END IF;
END;

How to use named error conditions:

CREATE PROCEDURE test_error(n INT)
BEGIN
   DECLARE `too_big` CONDITION FOR SQLSTATE '45000';
   IF n > 10 THEN
      SIGNAL `too_big`;
   END IF;
END;

In this example, we’ll define a HANDLER for an error code. When the error occurs, we SIGNAL a more informative error which makes sense for our procedure:

CREATE PROCEDURE test_error()
BEGIN
   DECLARE EXIT HANDLER
   FOR 1146
   BEGIN
      SIGNAL SQLSTATE '45000' SET
      MESSAGE_TEXT = 'Temporary tables not found; did you call init() procedure?';
   END;
   -- this will produce a 1146 error
   SELECT `c` FROM `temptab`;
END;

See Also

  • Diagnostics Area
  • RESIGNAL
  • HANDLER
  • Stored Routines
  • MariaDB Error Codes
Источник

Привет Хабр! Хочу рассказать в статье мой опыт реализации бизнес логики (БЛ) в 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 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$

Итого

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

Источник

Понравилась статья? Поделить с друзьями:
  • Mysql query error что значит
  • Mysql query error битрикс при авторизации
  • Mysql query error log
  • Mysql query error index column size too large the maximum column size is 767 bytes
  • Mysql query error 2006 mysql server has gone away 400 select