Как в хранимой функции гарантировать одновременное выполнение или одновременное невыполнение двух INSERT в разные таблицы?

Question

[Иван Мельников]

В MySQL 5.7 имеются две таблицы:

СREATE TABLE Maerial(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255)
);
СREATE TABLE Maerial2(
material_id INT NOT NULL PRIMARY KEY,
date DATE,
FOREIGN KEY (material_id) REFERENCES Material (id)
);

Нужно сделать так, чтобы запись об одном объекте делалась сразу в две таблицы. Ситуация, когда запись сделана в таблицу Material, а в таблицу Material2 – нет (например, данные не прошли валидацию при INSERT), недопустима!
Предполагается, что внешнее приложение, которое будет писать в базу, должно писать через хранимую функцию (приложение должно получать обратно id записи). Я вижу эту функцию так:

CREATE FUNCTION insert_material (n VARCHAR(255), d DATE) RETURNS INT
BEGIN
DECLARE idd INT;
INSERT INTO Material(name) VALUE(n);
SET idd = LAST_INSERT_ID();
INSERT INTO Material2(material_id, date) VALUE(idd, d);
RETURN idd;
END

В идеале колонки первичных ключей этих таблиц должны быть идентичными. Но проблема в том, что второй INSERT в функции может не пройти... В этом случае нужно будет откатить первый INSERT. Как это сделать? Нужно гарантировать, чтобы записи одновременно делались в обе таблицы, либо одновременно не делались.

Answer 1

[coderisimo]

Используйте транзакции , в чем вопрос! )

Comments

[coderisimo]

вам нужно определить обработчики sqlexception и sqlwarning где в случае ошибки будет совершен роллбэк.

DELIMITER $$

CREATE PROCEDURE `transaction_sp` ()
LANGUAGE SQL
DETERMINISTIC
SQL SECURITY DEFINER
COMMENT 'Opa opa!))) '
BEGIN

DECLARE exit handler for sqlexception
  BEGIN
    -- ERROR
  ROLLBACK;
END;
 
DECLARE exit handler for sqlwarning
 BEGIN
    -- WARNING
 ROLLBACK;
END;

START TRANSACTION;
  INSERT INTO table_name (id, name, address) values ('1','test!','toster.ru');
  UPDATE second_table set name="Kolya" where id=4;
COMMIT;
END
$$

[Иван Мельников]

Так?

CREATE FUNCTION insert_material (n VARCHAR(255), d DATE) RETURNS INT
BEGIN
    DECLARE idd INT;
    START TRANSACTION;
        INSERT INTO Material(name) VALUE(n);
        SET idd = LAST_INSERT_ID();
        INSERT INTO Material2(material_id, date) VALUE(idd, d);
    COMMIT;
RETURN idd;
END

[coderisimo]

Иван Мельников,
Иван, пробуйте! Я же описал идею и необходимость вот этого

DECLARE exit handler for sqlexception
  BEGIN
    -- ERROR
  ROLLBACK;
END;
 
DECLARE exit handler for sqlwarning
 BEGIN
    -- WARNING
 ROLLBACK;
END;

дальше сами )))

[Иван Мельников]

coderisimo, спасибо!

[coderisimo]

Иван Мельников, удачи ! )