Trigger sem atrapalhar execução tabela principal

Todas as Respostas

• 

  

  0

  Entrar para Votar

  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
• 

  

  0

  Entrar para Votar

  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
• 

  

  0

  Entrar para Votar

  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
• 

  

  0

  Entrar para Votar

  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
• 

  

  0

  Entrar para Votar

  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
• 

  

  0

  Entrar para Votar

  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
• 

  

  0

  Entrar para Votar

  Como seria a solução para resolver este problema no sql server 2005?

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

  

  0

  Entrar para Votar

  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
• 

  

  0

  Entrar para Votar

  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
• 

  

  0

  Entrar para Votar

  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
• 

  

  0

  Entrar para Votar

  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