Register sale with its retail detail through transactions in a stored procedure MYSQL

from the programming language in a method you send the header, with the stored procedure ups_venta_insertar, this will return the idventa and then you go through the grid where you have the lines that make up the detail of the sale and send it to the other stored procedure ups_venta_detalle_insertar:

-- CABECERA VENTA (tabla venta)

DELIMITER $$
CREATE DEFINER='root'@'localhost' PROCEDURE 'usp_venta_insertar'(
IN 'p_fecha' datetime,
IN 'p_id_cliente' int(10) UNSIGNED,
IN 'p_subtotal' decimal,
IN 'p_descuentos' decimal,
IN 'p_total' decimal,
OUT 'p_idventa' int(11) UNSIGNED,
OUT 'p_mierror_cod' int(11),
OUT 'p_mierror_msg'  text)
MODIFIES SQL DATA
COMMENT 'Insertar Venta'
BEGIN

DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
BEGIN

ROLLBACK;
    GET DIAGNOSTICS CONDITION 1 @RETURNED_SQLSTATE = RETURNED_SQLSTATE, @MYSQL_ERRNO = MYSQL_ERRNO, @MESSAGE_TEXT = MESSAGE_TEXT;
    SET p_mierror_cod = -10;
    SET p_mierror_msg = CONCAT("ERRNO: ",@MYSQL_ERRNO, ", SQLSTATE: ", @RETURNED_SQLSTATE, ", TEXT: ", @MESSAGE_TEXT);

END;

START TRANSACTION;

    SET p_idventa = 0;

    INSERT INTO 'venta' ('id_cliente', 'subtotal', 'descuentos', 'total')
      VALUES ('p_id_cliente', 'p_subtotal', 'p_descuentos', 'p_total');

    SELECT LAST_INSERT_ID() INTO p_idventa;

     -- Exitosamente
    SET p_mierror_cod = 1;
    SET p_mierror_msg = 'Venta insertado correctamente';

COMMIT;

END$$
DELIMITER ;

-- LINEA VENTA (TABLA venta_detalle)
DELIMITER $$
CREATE DEFINER='root'@'localhost' PROCEDURE 'usp_venta_detalle_insertar'(
IN 'p_id_venta' int(10) UNSIGNED,
IN 'p_id_producto' int(10) UNSIGNED,
IN 'p_cantidad' int,
IN 'p_precio_unitario' decimal,
IN 'p_importe' decimal,
OUT 'p_idventa_detalle' int(11) UNSIGNED,
OUT 'p_mierror_cod' int(11),
OUT 'p_mierror_msg'  text)
MODIFIES SQL DATA
COMMENT 'Insertar Venta Detalle (linea)'
BEGIN

DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
BEGIN

ROLLBACK;
    GET DIAGNOSTICS CONDITION 1 @RETURNED_SQLSTATE = RETURNED_SQLSTATE, @MYSQL_ERRNO = MYSQL_ERRNO, @MESSAGE_TEXT = MESSAGE_TEXT;
    SET p_mierror_cod = -10;
    SET p_mierror_msg = CONCAT("ERRNO: ",@MYSQL_ERRNO, ", SQLSTATE: ", @RETURNED_SQLSTATE, ", TEXT: ", @MESSAGE_TEXT);

END;

START TRANSACTION;

    SET p_idventa_detalle = 0;

    INSERT INTO 'venta_detalle' ('id_venta', 'id_producto','cantidad','precio_unitario','importe')
      VALUES ('p_id_venta','p_id_producto','p_cantidad','p_precio_unitario','p_importe');

    SELECT LAST_INSERT_ID() INTO p_idventa_detalle;

     -- Exitosamente
    SET p_mierror_cod = 1;
    SET p_mierror_msg = 'Venta Detalle (Linea) insertado correctamente';

COMMIT;

END$$
DELIMITER ;

Good morning Juan!

Another user commented that you can do this on the front-end since you do not know, a priori, how many and what products the sale will have. If possible, describe your scenario in more detail so we can help you better.

For example, when you open the window for new sales you can insert a new row in the sale table and set it to a special state to know that this sale is in "Armed". Then, in the application you store the ID of the sale and at the moment in which a user adds a new product to the detail then you send the ID of the sale and insert the detail in sale_detail. The state will serve you to be protected against a power outage for example, then you can recover sales that have reserved stock and that had not yet been confirmed.

The critical code will be in the allocation to the detail of the sale and the decrease of the stock. That way, if several sellers are making sales in your system you will never "step" on the stock, because if you add to your sale X amount of an item then another seller will already see 0 of stock and will not be able to add it for sale. On the other hand, if you do everything in the same stored procedure you will send the detail once the sale is already armed, but in the period in which the sellers set up the sale your base could be inconsistent. For example, vendor A adds 2 units of the product X because the stock is 2, before vendor A presses on saving vendor vendor B adds 1 unit of the same product X because the stock is still 2. The first one to save will have taken the stock while the other will receive an error. It is preferable not to allow adding the article if the stock is less than the amount to be added. Once the item has been added to the sale, the stock is reserved.

On the other hand, the data in a parameter when calling the MySQL stored procedure could be sent as an array (in PostgreSQL I have tried it). In this way you could send several IDs, quantities, etc., in the same parameter and in the database, go through to make the inserts in a transaction as you thought.

I hope I have been of help! Greetings!

I have done processes like these, and the most convenient thing has been to send an xml file with the header and the detail to a single SP in which both INSERTS are inside a transaction. I have done it in SQL Server, I imagine that MySQL should also be able to do it. Greetings.

I would recommend you use a unique code only that you have to do before your registration in the SALE table:

 SELECT UUID() INTO @codigo_unico;

That you will have to save it inside a field type char or varchar, thus you avoid the problem of which the number of the id is repeated. or use another. This unique code will be used in SALE and will be the FK in DETAIL_SALE. If you need more security in the process, you must use START TRANSACTION. and everything that goes with it.