none
Chamar PROCEDURE dentro de uma TRIGGER - The transaction ended in the trigger. The batch has been aborted. RRS feed

  • Pergunta

  • Boa tarder Srs,

    Estou com uma dificuldade ao chamar uma procedure dentro de uma trigger.

    Preciso que uma procedure seja disparada todas as vezes que ocorrer um insert em uma tabela, pensei em utilizar uma trigger, é necessário que eu monitore a chamada da procedure.

    Segue abaixo o meu cenário.

    ------------------------ tabela
    create table tbl_z
    (
    	a varchar(100),
    	b varchar(200)
    )
    
    ------------------------ procedure
    create procedure proc_z as begin
    select 'ok' ok
    end
    
    ------------------------ trigger
    alter trigger trigger_z on tbl_z after insert
    as begin
    	set nocount on;
    	declare @a varchar(100),@b varchar(100)
    
    	select @a = a, @b = b from inserted
    			commit tran
    			begin try
    				exec proc_z @a,@b
    			end try
    			begin catch
    				print error_message()
    			end catch
    end
    
    ------------------------ insert
    insert into tbl_z (a,b)
    values ('ooo','bbb')


    O erro que ocorre é :  

    Msg 3609, Level 16, State 1, Line 1
    The transaction ended in the trigger. The batch has been aborted.

    Sei que estou passando parâmetros para uma procedure que não recebe, mas são erros iguais a esses que gostaria de capturar.

    Poderiam me ajudar ?

    Obrigado.


    sábado, 29 de junho de 2013 17:12

Respostas

  • Meu caro, o erro está acontecendo porque você está encerrando a transação dentro da trigger através do comando "COMMIT TRAN".

    Este link explica que isso é possível, mas que irá causar erros, dependendo do caso:

    http://msdn.microsoft.com/en-us/library/ms187844(v=sql.105).aspx

    http://msdn.microsoft.com/pt-br/library/ms187844(v=sql.105).aspx (português)

    A questão é que o "COMMIT TRAN" está causando a transação que disparou a trigger (que no caso é transação implicita criada pelo INSERT) ser encerrada.

    Com isso, a variável @@TRANCOUNT, que contém a quantidade de transações abertas, fica com o valor 0 quando a trigger finaliza. Isso faz com que o sql server dispare o erro 3609.

    Se você colocar um BEGIN TRAN no final da execuçãod a trigger seu código vai funcionar sem erros, pois o mesmo vai fazer com que a variável @@TRANCOUNT fique com o valor 1.

    O erro em questão esta causando a transacao entrar em um modo conhecido por "Uncommitable state" (http://msdn.microsoft.com/pt-br/library/ms179296.aspx | http://msdn.microsoft.com/en-us/library/ms179296.aspx). Neste modo a transacao ja nao pode ser mais comitada. Porem voce nao vai ver esse erro pois seu block try catch nao esta mais dentro de uma transacao porque ja  fechou a mesma executando um COMMIT TRAN.

    Mas eu ainda nao conseguir entender exatamente o que você quer fazer.

    Voce quer pegar erros de execucao da procedure ???


    []'s | Rodrigo Ribeiro Gomes | MCTS/MCITP Dev/DBA

    domingo, 30 de junho de 2013 21:01

Todas as Respostas

  • Meu caro, o erro está acontecendo porque você está encerrando a transação dentro da trigger através do comando "COMMIT TRAN".

    Este link explica que isso é possível, mas que irá causar erros, dependendo do caso:

    http://msdn.microsoft.com/en-us/library/ms187844(v=sql.105).aspx

    http://msdn.microsoft.com/pt-br/library/ms187844(v=sql.105).aspx (português)

    A questão é que o "COMMIT TRAN" está causando a transação que disparou a trigger (que no caso é transação implicita criada pelo INSERT) ser encerrada.

    Com isso, a variável @@TRANCOUNT, que contém a quantidade de transações abertas, fica com o valor 0 quando a trigger finaliza. Isso faz com que o sql server dispare o erro 3609.

    Se você colocar um BEGIN TRAN no final da execuçãod a trigger seu código vai funcionar sem erros, pois o mesmo vai fazer com que a variável @@TRANCOUNT fique com o valor 1.

    O erro em questão esta causando a transacao entrar em um modo conhecido por "Uncommitable state" (http://msdn.microsoft.com/pt-br/library/ms179296.aspx | http://msdn.microsoft.com/en-us/library/ms179296.aspx). Neste modo a transacao ja nao pode ser mais comitada. Porem voce nao vai ver esse erro pois seu block try catch nao esta mais dentro de uma transacao porque ja  fechou a mesma executando um COMMIT TRAN.

    Mas eu ainda nao conseguir entender exatamente o que você quer fazer.

    Voce quer pegar erros de execucao da procedure ???


    []'s | Rodrigo Ribeiro Gomes | MCTS/MCITP Dev/DBA

    domingo, 30 de junho de 2013 21:01
  • Rodrigo,

    Obrigado pela resposta!

    Realmente resolveu meu problema.

    Então eu quero pegar qualquer erro na chamada dessa procedure, pois ela não ficará no meu banco de dados e sim em um banco de dados do fornecedor.

    Obrigado.

    Abraços

    segunda-feira, 1 de julho de 2013 03:07
  • Bom você deve estar ciente que essa abordagem escolhida pode causar erros na lógica, pois você está comitando a transação, e devolvendo um nova.

    Assim se o usuario fazer um ROLLBACK, ele nao irá mais desfazer o INSERT, e sim as alterações feitas após a transação.

    Isso pode causar algum problema de lógica na aplicação.


    []'s | Rodrigo Ribeiro Gomes | MCTS/MCITP Dev/DBA

    segunda-feira, 1 de julho de 2013 12:30