Triggers

CREATE DEFINER=`root`@`localhost` TRIGGER `desarrollo`.`venta_BEFORE_INSERT` BEFORE INSERT ON `venta` FOR EACH ROW
BEGIN
    SET SQL_SAFE_UPDATES = 0;

    SET NEW.fecha_hora_venta = NOW();

    SET SQL_SAFE_UPDATES = 1;
END

CREATE DEFINER=`root`@`localhost` TRIGGER `desarrollo`.`pedido_BEFORE_INSERT` BEFORE INSERT ON `pedido` FOR EACH ROW
BEGIN
    SET SQL_SAFE_UPDATES = 0;

    SET NEW.fecha_hora = NOW();

    SET SQL_SAFE_UPDATES = 1;
END

CREATE DEFINER=`root`@`localhost` TRIGGER `desarrollo`.`pedido_AFTER_INSERT` AFTER INSERT ON `pedido` FOR EACH ROW
BEGIN
    SET SQL_SAFE_UPDATES = 0;
    INSERT INTO linea_pedido(precio_producto, cantidad, pedido_id, producto_id)
    SELECT producto.precio, carrito.cantidad, NEW.id AS pedido_id, inventario.producto_id
    FROM carrito
    INNER JOIN inventario
        ON carrito.inventario_id = inventario.id
    INNER JOIN producto
        ON inventario.producto_id = producto.id
    WHERE inventario.sucursal_id = NEW.sucursal_id
        AND carrito.cliente_id = NEW.cliente_id;

    DELETE
    FROM carrito
    USING carrito INNER JOIN inventario
    ON carrito.inventario_id = inventario.id
    WHERE
        carrito.cliente_id = NEW.cliente_id
        AND inventario.sucursal_id = NEW.sucursal_id;
    SET SQL_SAFE_UPDATES=1;
END

CREATE DEFINER=`root`@`localhost` TRIGGER `desarrollo`.`pedido_AFTER_DELETE` AFTER DELETE ON `pedido` FOR EACH ROW
BEGIN
SET SQL_SAFE_UPDATES = 0;

UPDATE inventario
INNER JOIN linea_pedido
    ON inventario.producto_id = linea_pedido.producto_id
SET inventario.stock = inventario.stock + linea_pedido.cantidad
WHERE inventario.sucursal_id = OLD.sucursal_id
    AND linea_pedido.pedido_id = OLD.id;

SET SQL_SAFE_UPDATES = 1;
END

CREATE DEFINER=`root`@`localhost` TRIGGER `desarrollo`.`carrito_AFTER_INSERT` AFTER INSERT ON `carrito` FOR EACH ROW
BEGIN
SET SQL_SAFE_UPDATES = 0;

UPDATE inventario
SET inventario.stock = inventario.stock - NEW.cantidad
WHERE inventario.id = NEW.inventario_id;

SET SQL_SAFE_UPDATES = 1;
END