SQL SERVER COM TRANSACTION
Pessoal, boa noite!
Preciso de informação ou até mesmo sugestão de como fazer o seguinte processo:
Tenho uma tabela chamada "planilha", onde essa tabela apenas gera um ID que será referência para todas as tabelas onde ocorrerem transações relacionadas:
Exemplo: Tabela PEDIDO, possui o campo "idplanilha", tabela PEDIDO_ITEM também possui o campo "idplanilha"
O campo "idplanilha" da tabela planilha é um campo identity gerado automaticamente pelo banco.
O meu problema está aqui: Quando efetuo uma transação eu utilizo transaction para dar um commit ou rollbak em caso de falha.
O problema é que como todas as comandos estão na mesma transação, eu consigo fazer o insert na tabela planilha e retorno o idplanilha inserido, porém durante a mesma transação ao tentar inserir na tabela PEDIDO ou PEDIDO_ITEM referenciado o campo idplanilha ele dá o erro de chave estrangeira, justamente por não ter ocorrido o commit.
Como posso resolver essa situação, porque digamos que de um erro no último INSERT, ao utilizar rollbak ele deverá desconsiderar os comandos, inclusive o idplanilha que foi gerado na tabela PLANILHA.
Obrigado pessoal.
Preciso de informação ou até mesmo sugestão de como fazer o seguinte processo:
Tenho uma tabela chamada "planilha", onde essa tabela apenas gera um ID que será referência para todas as tabelas onde ocorrerem transações relacionadas:
Exemplo: Tabela PEDIDO, possui o campo "idplanilha", tabela PEDIDO_ITEM também possui o campo "idplanilha"
O campo "idplanilha" da tabela planilha é um campo identity gerado automaticamente pelo banco.
O meu problema está aqui: Quando efetuo uma transação eu utilizo transaction para dar um commit ou rollbak em caso de falha.
O problema é que como todas as comandos estão na mesma transação, eu consigo fazer o insert na tabela planilha e retorno o idplanilha inserido, porém durante a mesma transação ao tentar inserir na tabela PEDIDO ou PEDIDO_ITEM referenciado o campo idplanilha ele dá o erro de chave estrangeira, justamente por não ter ocorrido o commit.
Como posso resolver essa situação, porque digamos que de um erro no último INSERT, ao utilizar rollbak ele deverá desconsiderar os comandos, inclusive o idplanilha que foi gerado na tabela PLANILHA.
Obrigado pessoal.
Alterado em 23/09/2023 17:47:29
Melhor opção que encontrei a mais de 20 anos, é claro que pode e até deve existir melhor.
Gere uma sequencia vc mesmo em uma tabela de sequencias.
A Grosso modo:
Recupere essa numeração, exemplo, ID, antes de cada pedido, utilize essa ID nos pedidos_itens, onde a coluna ID seja chave primaria, se algum outro usuário em outro local tentar utilizar a mesma ID, o erro de duplicidade ira acontecer, trate o erro realizando um acréscimo nessa ID+1 até que o sistema aceite essa nova ID.
Ao final do processo, antes mesmo do commit porque tudo deve ficar na mesma transação altere o valor dessa ID ou ID+1 na tabela de sequencias.
Gere uma sequencia vc mesmo em uma tabela de sequencias.
A Grosso modo:
Recupere essa numeração, exemplo, ID, antes de cada pedido, utilize essa ID nos pedidos_itens, onde a coluna ID seja chave primaria, se algum outro usuário em outro local tentar utilizar a mesma ID, o erro de duplicidade ira acontecer, trate o erro realizando um acréscimo nessa ID+1 até que o sistema aceite essa nova ID.
Ao final do processo, antes mesmo do commit porque tudo deve ficar na mesma transação altere o valor dessa ID ou ID+1 na tabela de sequencias.
Na inserção do ID na tabela PEDIDO concordo que daria certo, porém na tabela PEDIDO_ITEM teria um código ID_PEDIDO que é o ID da tabela PEDIDO. Existe um relacionamento entre os campos, que ao inserir na tabela PEDIDO_ITEM dará um erro de FOREIGN KEY alegando não existir o ID na tabela PEDIDO.
Teria alguma tratativa para isso?
Grato.
Teria alguma tratativa para isso?
Grato.
Citação:Na inserção do ID na tabela PEDIDO concordo que daria certo, porém na tabela PEDIDO_ITEM teria um código ID_PEDIDO que é o ID da tabela PEDIDO. Existe um relacionamento entre os campos, que ao inserir na tabela PEDIDO_ITEM dará um erro de FOREIGN KEY alegando não existir o ID na tabela PEDIDO.
Teria alguma tratativa para isso?
Desculpe, fazendo uma correção.
Onde:
"Recupere essa numeração, exemplo, ID, antes de cada pedido, utilize essa ID nos pedidos_itens, onde a coluna ID seja chave primaria"
entenda:
Recupere essa numeração, exemplo, ID, antes de cada pedido, utilize essa ID nos (pedidos) e não na (pedidos itens), onde a coluna ID seja chave primaria.
Porque quando definido o ID da pedidos, você terá definido o ID da Pedidos Itens sem problema de duplicidade.
Seria mais fácil se você mostrasse seu código, pois o processo que você descreve está correto e deveria funcionar, o máximo que consigo imaginar é que ou você não está conseguindo pegar o novo idplanilha que foi gerado ou não está tudo na mesma transação como você imagina.
O que você está usando para pegar o idplanilha novo? Está usando algo como @@IDENTITY ou SCOPE_IDENTITY() pra isso?
O que você está usando para pegar o idplanilha novo? Está usando algo como @@IDENTITY ou SCOPE_IDENTITY() pra isso?
In SQL Server, a transaction is a set of actions that are executed together. If all actions succeed, the changes are saved; if any action fails, all changes are undone.
Ultimamente não estou quebrando muito a cabeça kkkkkkkkkkkkkkkk
peguei sua duvida e fui ali no oraculo então veja ae
Solução Recomendada
sql
Copiar
Editar
BEGIN TRANSACTION;
DECLARE @idplanilha INT;
-- Inserindo na tabela planilha e capturando o ID gerado
INSERT INTO planilha DEFAULT VALUES;
SET @idplanilha = SCOPE_IDENTITY();
-- Inserindo na tabela PEDIDO referenciando o idplanilha gerado
INSERT INTO PEDIDO (idplanilha, outro_campo)
VALUES (@idplanilha, 'algum_valor');
-- Inserindo na tabela PEDIDO_ITEM referenciando o idplanilha gerado
INSERT INTO PEDIDO_ITEM (idplanilha, outro_campo)
VALUES (@idplanilha, 'outro_valor');
-- Confirma a transação
COMMIT;
Explicação
BEGIN TRANSACTION; ? Inicia uma transação para garantir que todas as inserções sejam atômicas.
INSERT INTO planilha DEFAULT VALUES; ? Insere um novo registro na tabela planilha, gerando um idplanilha automaticamente.
SET @idplanilha = SCOPE_IDENTITY(); ? Captura o último ID gerado dentro do mesmo escopo da transação, garantindo que o ID correto seja usado.
Inserções subsequentes (PEDIDO e PEDIDO_ITEM) ? Agora que temos o ID, podemos referenciá-lo sem erro de chave estrangeira.
COMMIT; ? Confirma todas as inserções. Se algo falhar, nenhuma alteração será feita.
Se houver erro, faça ROLLBACK
Caso algum erro aconteça durante a transação, você pode garantir que tudo seja revertido com TRY...CATCH:
sql
Copiar
Editar
BEGIN TRANSACTION;
DECLARE @idplanilha INT;
BEGIN TRY
-- Inserindo na tabela planilha e capturando o ID gerado
INSERT INTO planilha DEFAULT VALUES;
SET @idplanilha = SCOPE_IDENTITY();
-- Inserindo na tabela PEDIDO referenciando o idplanilha gerado
INSERT INTO PEDIDO (idplanilha, outro_campo)
VALUES (@idplanilha, 'algum_valor');
-- Inserindo na tabela PEDIDO_ITEM referenciando o idplanilha gerado
INSERT INTO PEDIDO_ITEM (idplanilha, outro_campo)
VALUES (@idplanilha, 'outro_valor');
-- Confirma a transação
COMMIT;
END TRY
BEGIN CATCH
-- Se houver erro, desfaz todas as operações
ROLLBACK;
PRINT 'Erro ao inserir dados. Transação revertida.';
END CATCH;
Conclusão
Evite COMMIT antes das outras inserções ? O SCOPE_IDENTITY() permite que você use o ID dentro da mesma transação.
TRY...CATCH com ROLLBACK ? Garante que se algo der errado, nada será gravado no banco.
Não use @@IDENTITY ? Ele pode retornar o ID de um TRIGGER, causando inconsistências.
Dessa forma, você garante que todas as inserções sejam feitas de forma segura e consistente! ??
peguei sua duvida e fui ali no oraculo então veja ae
Solução Recomendada
sql
Copiar
Editar
BEGIN TRANSACTION;
DECLARE @idplanilha INT;
-- Inserindo na tabela planilha e capturando o ID gerado
INSERT INTO planilha DEFAULT VALUES;
SET @idplanilha = SCOPE_IDENTITY();
-- Inserindo na tabela PEDIDO referenciando o idplanilha gerado
INSERT INTO PEDIDO (idplanilha, outro_campo)
VALUES (@idplanilha, 'algum_valor');
-- Inserindo na tabela PEDIDO_ITEM referenciando o idplanilha gerado
INSERT INTO PEDIDO_ITEM (idplanilha, outro_campo)
VALUES (@idplanilha, 'outro_valor');
-- Confirma a transação
COMMIT;
Explicação
BEGIN TRANSACTION; ? Inicia uma transação para garantir que todas as inserções sejam atômicas.
INSERT INTO planilha DEFAULT VALUES; ? Insere um novo registro na tabela planilha, gerando um idplanilha automaticamente.
SET @idplanilha = SCOPE_IDENTITY(); ? Captura o último ID gerado dentro do mesmo escopo da transação, garantindo que o ID correto seja usado.
Inserções subsequentes (PEDIDO e PEDIDO_ITEM) ? Agora que temos o ID, podemos referenciá-lo sem erro de chave estrangeira.
COMMIT; ? Confirma todas as inserções. Se algo falhar, nenhuma alteração será feita.
Se houver erro, faça ROLLBACK
Caso algum erro aconteça durante a transação, você pode garantir que tudo seja revertido com TRY...CATCH:
sql
Copiar
Editar
BEGIN TRANSACTION;
DECLARE @idplanilha INT;
BEGIN TRY
-- Inserindo na tabela planilha e capturando o ID gerado
INSERT INTO planilha DEFAULT VALUES;
SET @idplanilha = SCOPE_IDENTITY();
-- Inserindo na tabela PEDIDO referenciando o idplanilha gerado
INSERT INTO PEDIDO (idplanilha, outro_campo)
VALUES (@idplanilha, 'algum_valor');
-- Inserindo na tabela PEDIDO_ITEM referenciando o idplanilha gerado
INSERT INTO PEDIDO_ITEM (idplanilha, outro_campo)
VALUES (@idplanilha, 'outro_valor');
-- Confirma a transação
COMMIT;
END TRY
BEGIN CATCH
-- Se houver erro, desfaz todas as operações
ROLLBACK;
PRINT 'Erro ao inserir dados. Transação revertida.';
END CATCH;
Conclusão
Evite COMMIT antes das outras inserções ? O SCOPE_IDENTITY() permite que você use o ID dentro da mesma transação.
TRY...CATCH com ROLLBACK ? Garante que se algo der errado, nada será gravado no banco.
Não use @@IDENTITY ? Ele pode retornar o ID de um TRIGGER, causando inconsistências.
Dessa forma, você garante que todas as inserções sejam feitas de forma segura e consistente! ??
Faça seu login para responder