SQL: Executar Diversas Instruções de Modo que Se uma Falhar Todas Falham

Hoje iremos ver como usar SQL para executar diversas instruções de modo que se uma falhar todas Falham e nada será alterado.

Para fazer isso, usamos uma instrução SQL chamada de BEGIN TRANSACTION que é uma sequência de uma ou mais instruções SQL que são tratadas como uma única unidade de trabalho. Isso significa que todas as operações dentro do BEGIN TRANSACTION são executadas com sucesso ou nenhuma é executada.

O termo BEGIN TRANSACTION traduzido para o português quer dizer, exatamente, INICIAR TRANSAÇÃO.

Usamos a seguinte sintaxe para BEGIN TRANSACTION:

BEGIN TRANSACTION;

adicione a primeira instrução aqui;

adicione a segunda instrução aqui;

adicione a terceira instrução aqui;

COMMIT;

Veja que iniciamos com BEGIN TRANSACTION; e terminamos com COMMIT; e é apenas isso.

Exemplos usando PHP

Segue um exemplo usando PHP onde realizo 3 exclusões:

<?php

// Iniciar a transação
mysqli_begin_transaction($con);

$sql1 = "DELETE FROM recibo where numero_recibo='$recibo';";
mysqli_query($con, $sql1);

$sql2 =  "DELETE FROM pagamento where recibo='$recibo';";
mysqli_query($con, $sql2);

$sql3 =  "DELETE FROM complemento where recibo='$recibo';";
mysqli_query($con, $sql3);

mysqli_commit($con);


?>

O código acima é funcional para a transação de exclusão, mas podemos torná-lo mais robusto, melhorando a legibilidade e incorporando alguns princípios de boas práticas. Há alguns riscos que devemos tratar:

• Uso de Prepared Statements: Recomenda-se utilizar prepared statements para evitar ataques de injeção SQL. No código atual, o valor de $recibo é diretamente interpolado nas consultas, o que pode ser arriscado.
• Uso de Funções para Reutilização de Código: vamos criar uma função para executar operações de exclusão, reduzindo a repetição de código.
• Tratamento de Erros: podemos adicionar tratamento de erros para lidar com possíveis falhas nas consultas SQL.

<?php

include "conexaoBD.php";

// Função para execução de consultas de exclusão
function executarExclusao($con, $tabela, $campo, $valor) {
    $sql = "DELETE FROM $tabela WHERE $campo = ?";
    $stmt = mysqli_prepare($con, $sql);

    if ($stmt) {
        mysqli_stmt_bind_param($stmt, 's', $valor);
        mysqli_stmt_execute($stmt);
        mysqli_stmt_close($stmt);
    } else {
        // Tratar erro de preparação da consulta
        die("Erro ao preparar a consulta: " . mysqli_error($con));
    }
}

// Número do recibo
$recibo = $_GET["recibo"];

// Iniciar a transação
mysqli_begin_transaction($con);

try {
    // Executar operações de exclusão
    executarExclusao($con, 'recibo', 'numero_recibo', $recibo);
    executarExclusao($con, 'pagamento', 'recibo', $recibo);
    executarExclusao($con, 'complemento', 'recibo', $recibo);

    // Commit da transação
    mysqli_commit($con);

    echo "Exclusões bem-sucedidas. Transação confirmada.";
} catch (Exception $e) {
    // Rollback da transação em caso de erro
    mysqli_rollback($con);
    echo "Erro ao excluir: " . $e->getMessage();
}

// Fechar conexão
mysqli_close($con);

?>

Acima, está uma versão que usa prepared statements para melhorar a segurança e a leitura do código. Além disso, a função executarExclusao pode ser reutilizada para outras operações de exclusão no futuro.

Exemplos usando apenas SQL

Vejamos um exemplo usando apenas SQL:

-- Iniciar a transação
BEGIN TRANSACTION;

-- Transferir fundos da conta de origem para a conta de destino
UPDATE ContasBancarias
SET Saldo = Saldo - 1000
WHERE NumeroConta = '123';

UPDATE ContasBancarias
SET Saldo = Saldo + 1000
WHERE NumeroConta = '456';

-- Commit para confirmar as alterações
COMMIT;

Neste exemplo, a transação garante que ambas as atualizações ocorram ou nenhuma ocorra. Se algo der errado durante o processo de transferência, você pode executar um ROLLBACK para reverter as alterações.

Segue outro exemplo,

-- Iniciar a transação
BEGIN TRANSACTION;

-- Atualizar informações do cliente
UPDATE Clientes
SET Nome = 'Novo Nome'
WHERE IDCliente = 1;

-- Atualizar informações do pedido relacionado
UPDATE Pedidos
SET Status = 'Processado'
WHERE IDCliente = 1;

-- Commit para confirmar as alterações
COMMIT;

as atualizações nas tabelas Clientes e Pedidos ocorrem dentro de uma única transação. Se algo der errado durante o processo de atualização, você pode executar um ROLLBACK para garantir que ambas as tabelas permaneçam em um estado consistente.

ROLLBACK em BEGIN TRANSACTION

Se algo der errado durante o processo de atualização, podemos executar um ROLLBACK para garantir que ambas as tabelas permaneçam em um estado consistente.

A instrução ROLLBACK é usada para reverter as alterações realizadas durante uma transação que está sendo processada, cancelando todas as operações até o momento do ROLLBACK

-- Iniciar a transação
BEGIN TRANSACTION;

-- Operações dentro da transação
UPDATE Tabela1 SET Coluna1 = 'NovoValor' WHERE Condição1;
DELETE FROM Tabela2 WHERE Condição2;

-- Verificar se ocorreu algum erro ou condição que exige o rollback
IF AlgumaCondicao THEN
    -- Reverter as alterações e desfazer a transação
    ROLLBACK;
ELSE
    -- Confirmar as alterações e efetuar a transação
    COMMIT;
END IF;

Se AlgumaCondicao for avaliada como verdadeira (indicando um problema durante a execução da transação), a instrução ROLLBACK será executada.

Em situações reais, você substituiria “AlgumaCondicao” por uma expressão condicional relevante à sua aplicação. Por exemplo, você pode usar uma verificação de erro retornada pelo banco de dados, um teste de validação de negócios ou qualquer outra condição que determine se a transação deve ser confirmada (COMMIT) ou revertida (ROLLBACK).

Um exemplo mais prático é esse outro, onde não uso AlgumaCondicao, mas sim uma condição real.

-- Iniciar a transação
BEGIN TRANSACTION;

-- Operações dentro da transação
UPDATE Tabela1 SET Coluna1 = 'NovoValor' WHERE Condição1;
DELETE FROM Tabela2 WHERE Condição2;

-- Verificar se ocorreu algum erro ou condição que exige o rollback
IF @@ERROR <> 0 THEN
    -- Reverter as alterações e desfazer a transação
    ROLLBACK;
ELSE
    -- Confirmar as alterações e efetuar a transação
    COMMIT;
END IF;

A condição @@ERROR <> 0 verifica se o valor de @@ERROR é diferente de zero, o que indica que ocorreu um erro. Se essa condição for verdadeira, significa que a instrução SQL anterior gerou um erro

Veja esse outro exemplo,

-- Iniciar a transação
BEGIN TRANSACTION;

-- Atualizar o preço de um produto no pedido
UPDATE Pedidos
SET PrecoProduto = 150
WHERE NumeroPedido = 123;

-- Calcular o novo valor total do pedido
UPDATE Pedidos
SET ValorTotal = (SELECT SUM(PrecoProduto) FROM Pedidos WHERE NumeroPedido = 123)
WHERE NumeroPedido = 123;

-- Verificar se o novo valor total excede o limite permitido
DECLARE @LimiteMaximo DECIMAL(10, 2);
SET @LimiteMaximo = 1000.00;

IF (SELECT ValorTotal FROM Pedidos WHERE NumeroPedido = 123) > @LimiteMaximo
BEGIN
    -- Reverter as alterações e desfazer a transação
    ROLLBACK;
    PRINT 'A transação foi revertida devido a exceder o limite máximo.';
END
ELSE
BEGIN
    -- Confirmar as alterações e efetuar a transação
    COMMIT;
    PRINT 'A transação foi efetuada com sucesso.';
END;

Compatibilidade de Gerenciadores de Banco de Dados (SGBD) com a Instrução BEGIN TRANSACTION

A instrução BEGIN TRANSACTION é uma parte do padrão SQL e, portanto, é suportada pela maioria dos Sistemas de Gerenciamento de Banco de Dados (SGBDs) relacionais.

Podemos citar alguns SGBD:

• MariaDB
• MySQL
• PostgreSQL
• Microsoft SQL Server
• Oracle Database
• SQLite

A forna de usar a instrução pode variar entre esses SGBDs. Vamos ver alguns exemplos:

No MariaDB usamos assim,

BEGIN;
-- ou
START TRANSACTION;

No Microsoft SQL Server usamos apenas isso:

BEGIN TRANSACTION;

No MySQL, além da forma completa, podemos usar START TRANSACTION.

BEGIN;
-- ou
START TRANSACTION;

NoPostgreSQL usamos apenas isso:

BEGIN;

No Oracle Database usamos apenas isso:

BEGIN;

No SQLite usamos apenas isso:

BEGIN;

Em todos esses casos, a instrução BEGIN TRANSACTION ou uma forma equivalente é usada para iniciar uma transação no banco de dados.

Clique aqui para ver outros posts sobre SQL.

Exemplo de “BEGIN TRANSACTION;” em PHP

<?php
// Conectar ao banco de dados
$servername = "seu_servidor";
$username = "seu_usuario";
$password = "sua_senha";
$dbname = "seu_banco_de_dados";

$conn = mysqli_connect($servername, $username, $password, $dbname);

// Verificar a conexão
if (!$conn) {
    die("Falha na conexão: " . mysqli_connect_error());
}

// Iniciar a transação
mysqli_begin_transaction($conn);

try {
    // Operações dentro da transação
    $sql1 = "UPDATE Tabela1 SET Coluna1 = 'NovoValor' WHERE Condição1";
    mysqli_query($conn, $sql1);

    $sql2 = "DELETE FROM Tabela2 WHERE Condição2";
    mysqli_query($conn, $sql2);

    // Simular uma condição que exige rollback
    $algumaCondicao = false;
    if ($algumaCondicao) {
        // Lançar uma exceção para simular um erro
        throw new Exception("Condição que exige rollback.");
    }

    // Confirmar as alterações e efetuar a transação
    mysqli_commit($conn);
    echo "Transação efetuada com sucesso.";
} catch (Exception $e) {
    // Reverter as alterações e desfazer a transação em caso de exceção
    mysqli_rollback($conn);
    echo "A transação foi revertida devido a um erro: " . $e->getMessage();
}

// Fechar a conexão
mysqli_close($conn);
?>