locked
Trigger sem atrapalhar execução tabela principal RRS feed

  • Pergunta

  • Amigos, estou utilizando uma stored procedure através de uma trigger, porém se ocorrer algum erro na stored o conteúdo não é atualizado na tabela que contem a trigger, por exemplo, se incluir um registro na tabela e ocorre erro na stored o registro não é incluso na tabela principal. Gostaria que o erro na stored não atrapalhe o andamento da trigger que também não deve atrapalhar a inserção na tabela principal. Desde já agradeço.

    segunda-feira, 17 de dezembro de 2007 18:17

Todas as Respostas

  • Carlos,

     

    Esta stored procedure esta sendo executado dentro do Trigger?

     

    Mas e a stored procedure apresentar problemas, a execução do trigger será abortado, o SQL Server, vai levantar uma execução.

     

    Este algum bloco de tratamento de erro dentro da procedure?

    segunda-feira, 17 de dezembro de 2007 19:10
    Moderador
  • Boa Tarde Carlos,

     

    Por padrão, todas as ações executadas na trigger fazem parte da mesma transação que a disparou (no seu caso o INSERT ou o UPDATE). Para evitar esse problema, abra uma transação dentro da trigger, chame sua stored procedure e execute o COMMIT independente do que houver. Dessa forma, independente da stored procedure gerar um erro ou não, a ação que disparou a trigger irá ser efetivada (a menos que haja alguma violação de constraint).

     

    [ ]s,

     

    Gustavo

     

    PS: Esse tipo de dúvida é melhor resolvida na comunidade MSDN.

     

    segunda-feira, 17 de dezembro de 2007 19:11
  • Gustavo,

     

    Muito bem lembrado, todo o qualquer trigger trabalha sobre transações, ou seja, ele cria internamente um bloco de transação e tudo que estiver dentro dele será executado neste mesmo bloco, por isso, quando ocorre um erro na stored procedure o trigger para.

     

    Concordo plenamente em criar um novo bloco e executar a procedure fazendo uso de um Commit forçado.

    segunda-feira, 17 de dezembro de 2007 19:16
    Moderador
  • Boa Tarde,

     

    Nosso raciocínio estava "correto", mas como fiquei com uma "pulga atrás da orelha", a solução que postei não funcionou. Não sei se fiz algo errado, mas não obtive êxito. Vou pensar em outra solução.

     

    -- Cria os objetos

    CREATE TABLE tblCli (CODIGO INT, Nome VARCHAR(40))

    GO

    CREATE TABLE tblAud (UserName VARCHAR(40) PRIMARY KEY)

    GO

    CREATE PROCEDURE Pro

    AS

    BEGIN

    INSERT INTO tblAud VALUES (USER_NAME())

    END

    GO

    CREATE TRIGGER trg ON TblCli

    FOR INSERT

    AS

    BEGIN

    EXEC Pro

    END

    -- Insere sem problemas

    INSERT INTO TblCli VALUES (1, 'Alguém')

    SELECT * FROM tblAud

    SELECT * FROM tblCli

    -- Gera um erro por conta da tblAud

    INSERT INTO TblCli VALUES (2, 'Fulano')

    SELECT * FROM tblAud

    SELECT * FROM tblCli

    GO

    -- Altera a trigger

    ALTER TRIGGER trg ON TblCli

    FOR INSERT

    AS

    BEGIN

    EXEC Pro

    COMMIT

    END

    -- Gera erro

    INSERT INTO TblCli VALUES (2, 'Fulano')

    SELECT * FROM tblAud

    SELECT * FROM tblCli

    -- Altera a trigger

    ALTER TRIGGER trg ON TblCli

    FOR INSERT

    AS

    BEGIN

    EXEC Pro

    if @@error <> 0

    INSERT INTO TblCli SELECT * FROM INSERTED

    END

    -- Gera erro

    INSERT INTO TblCli VALUES (2, 'Fulano')

    SELECT * FROM tblAud

    SELECT * FROM tblCli

    DROP PROC pro

    DROP TABLE tblAud

    DROP TABLE tblCli

     

    [ ]s,

     

    Gustavo

    segunda-feira, 17 de dezembro de 2007 19:41
  • Pessoal, não seria interessante ele fazer um controle de transação dentro da procedure também?

     

    Pois no bloco da procedure ele não efetiva nada internamente em caso de erro.

     

     

    Abraços,

     

    segunda-feira, 17 de dezembro de 2007 19:51
  • Oi Pessoal,

     

    Tentei outras técnicas como o SAVEPOINT, GOTO, etc mas nenhuma funcionou (mesmo com o controle transacional dentro da SP). Creio que a estratégia irá levar em consideração o tipo de erro para funcionar (com a violação de PK não funcionou).

     

    Se fosse no SQL Server 2005, a solução era fácil, mas no SQL Server 2000 estou sem muita inspiração para conseguir fazer algo genérico, ou seja, que execute a trigger independente do erro. Estou começando a achar que não é possível de se fazer isso já que a trigger é síncrona. Alguém mais se habilita ?

     

    [ ]s,

     

    Gustavo

     

    segunda-feira, 17 de dezembro de 2007 20:39
  • Como seria a solução para resolver este problema no sql server 2005?

    terça-feira, 18 de dezembro de 2007 11:08
  • Carlos,

     

    Você poderia utilizar no SQL Server 2005, blocos Try..Catch.

     

    Veja abaixo o código de exemplo:

     

    USE AdventureWorks;
    GO
    -- Verify that the stored procedure does not already exist.
    IF OBJECT_ID ( 'usp_GetErrorInfo', 'P' ) IS NOT NULL 
        DROP PROCEDURE usp_GetErrorInfo;
    GO
    
    -- Create procedure to retrieve error information.
    CREATE PROCEDURE usp_GetErrorInfo
    AS
        SELECT
            ERROR_NUMBER() AS ErrorNumber,
            ERROR_SEVERITY() AS ErrorSeverity,
            ERROR_STATE() AS ErrorState,
            ERROR_PROCEDURE() AS ErrorProcedure,
            ERROR_LINE() AS ErrorLine,
            ERROR_MESSAGE() AS ErrorMessage;
    GO
    
    BEGIN TRY
        -- Generate divide-by-zero error.
        SELECT 1/0;
    END TRY
    BEGIN CATCH
        -- Execute error retrieval routine.
        EXECUTE usp_GetErrorInfo;
    END CATCH;
    terça-feira, 18 de dezembro de 2007 11:32
    Moderador
  • Bom Dia Carlos,

     

    Simulei um problema parecido com o seu. A mensagem de erro ainda é gerada, mas os registros são inseridos mesmo que a procedure dê problema. Veja se o atende (a solução é para 2005).

     

    CREATE TABLE T1 (CODIGO INT)

    CREATE TABLE T2 (CODIGO INT PRIMARY KEY)

    GO

     

    CREATE PROC PRO

    AS

    INSERT INTO T2 VALUES (1)

    GO

     

    CREATE TRIGGER TRG ON T1

    FOR INSERT

    AS

    SET XACT_ABORT OFF

    BEGIN

    BEGIN TRY

    EXEC PRO

    END TRY

    BEGIN CATCH

    END CATCH

    COMMIT

    END

     

    INSERT INTO T1 VALUES (1)

     

    SELECT * FROM T1

    SELECT * FROM T2

     

    INSERT INTO T1 VALUES (1)

     

    SELECT * FROM T1

    SELECT * FROM T2

     

    DROP PROC PRO

    DROP TABLE T1

    DROP TABLE T2

     

    [ ]s,

     

    Gustavo

    terça-feira, 18 de dezembro de 2007 12:16
  • Pessoal no 2000 ele pode analisar o @@ERROR se maior que 0 houve algum problema e ele pode realizar um ROLLBACK.

     

    Entenderam?

     

    Abraços,

    quarta-feira, 19 de dezembro de 2007 13:39
  • Olá Rodrigo,

     

    O problema é que alguns erros tem gravidade maior e vão abortar o processo sem nem ao menos chegarem no @@error. Como não sabemos o que ocorre quando a procedure falha, não saberemos se o @@error funcionaria. Com o SQL Server 2005, o Try Catch é mais tolerante.

     

    [ ]s,

     

    Gustavo

     

    quarta-feira, 19 de dezembro de 2007 15:58