Question with Table Update Trigger

Question

I have two tables, questions and answers, and would like to update the same according to what is selected: Sql Server Database 2005

Problem:

--usuário excluindo a pergunta (não está gravando a data da exclusão é não está excluindo as perguntas)
update TB_PERGUNTA set EXCLUIR_PERGUNTA = 'S' where IDPERGUNTA = 4 and IDUSUARIO = 1


--moderador excluindo a pergunta (não está gravando a data da exclusão é não está excluindo as perguntas)
update TB_PERGUNTA set BLOQUEIO_PERGUNTA = 'S' where IDPERGUNTA = 4 and IDMODERADOR = 7

Create table [TB_PERGUNTA]
(
    [IDPERGUNTA] Integer NOT NULL, UNIQUE ([IDPERGUNTA]),
    [IDCATEGORIA] Integer NOT NULL,
    [IDUSUARIO] Integer NOT NULL,
    [IDMODERADOR] Integer NOT NULL,
    [TITULO_PERGUNTA] Text NULL,
    [DESCRICAO_PERGUNTA] Text NULL,
    [BLOQUEIO_PERGUNTA] Char(1) NULL,
    [DESCRICAO_BLOQUEIO] Text NULL,
    [EXCLUIR_PERGUNTA] Char(1) NULL,
    [DATA_PERGUNTA] Datetime NULL,
    [DATA_EXCLUIR] Datetime NULL,
Primary Key ([IDPERGUNTA])
) 
go

Create table [TB_RESPOSTA]
(
    [IDRESPOSTA] Integer NOT NULL, UNIQUE ([IDRESPOSTA]),
    [IDPERGUNTA] Integer NOT NULL,
    [IDUSUARIO] Integer NOT NULL,
    [IDMODERADOR] Integer NOT NULL,
    [RESPOSTA] Text NULL,
    [RESPOSTA_SELECIONADA] Char(1) NULL,
    [NOTA_RESPOSTA] Numeric(3,2) NULL,
    [BLOQUEIO_RESPOSTA] Char(1) NULL,
    [DESCRICAO_BLOQUEIO] Text NULL,
    [DATA_RESPOSTA] Datetime NULL,
    [DATA_EXCLUIR] Datetime NULL,
Primary Key ([IDRESPOSTA],[IDPERGUNTA])
) 
go

I have Trigger:

--gatilho para atualiza a tabela de pergunta é resposta no caso de exclusão pelo usuário
--ou pelo moderador
CREATE TRIGGER TGR_TB_PERGUNTA_UPDATE
 ON [dbo].[TB_PERGUNTA]
 FOR UPDATE
AS
BEGIN
   DECLARE @EXCLUIRPERGUNTA CHAR
   DECLARE @BLOQUEIOPERGUNTA CHAR
   DECLARE @IDPERGUNTA INT
   DECLARE @IDUSUARIO INT
   DECLARE @IDMODERADOR INT

   --se o excluirpergunta receber (S) então o usuário está bloqueando a pergunta se (N) está abrindo a pergunta
   SELECT  @EXCLUIRPERGUNTA  = EXCLUIR_PERGUNTA  FROM TB_PERGUNTA INSERTED
   --se o bloqueiopergunta receber (S) então o moderador está bloqueando a pergunta se (N) está abrindo a pergunta
   SELECT  @BLOQUEIOPERGUNTA = BLOQUEIO_PERGUNTA FROM TB_PERGUNTA INSERTED
   --pega o ID da pergunta
   SELECT  @IDPERGUNTA       = IDPERGUNTA        FROM TB_PERGUNTA INSERTED
   --pega o id do usuário
   SELECT  @IDUSUARIO        = IDUSUARIO         FROM TB_PERGUNTA INSERTED
   --pega o id do moderador
   SELECT  @IDMODERADOR      = IDMODERADOR       FROM TB_PERGUNTA INSERTED   

    --pergunta excluida pelo usuário 
   IF ( @EXCLUIRPERGUNTA = 'S')
    BEGIN
      --excluir a pergunta
     UPDATE TB_PERGUNTA  SET  EXCLUIR_PERGUNTA = 'S', 
            DESCRICAO_BLOQUEIO = 'Excluido pelo usuário' ,
            DATA_EXCLUIR = CONVERT(VARCHAR, GETDATE(), 20)
            WHERE IDPERGUNTA = @IDPERGUNTA AND IDUSUARIO =  @IDUSUARIO 

          --excluir todas as respostas referente a pergunta
     UPDATE TB_RESPOSTA SET BLOQUEIO_RESPOSTA = 'S', 
            DATA_EXCLUIR = CONVERT(VARCHAR, GETDATE(), 20),
            DESCRICAO_BLOQUEIO = 'Excluido pelo usuário'  WHERE IDPERGUNTA = @IDPERGUNTA          
    END

    --pergunta bloqueada pelo moderador
   IF ( @BLOQUEIOPERGUNTA = 'S')
    BEGIN
     --excluir pergunta
     UPDATE TB_PERGUNTA  SET BLOQUEIO_PERGUNTA = 'S', 
          DESCRICAO_BLOQUEIO = 'Está pergunta foi bloqueada pelo moderador',  
          DATA_EXCLUIR = CONVERT(VARCHAR, GETDATE(), 20)
          WHERE IDPERGUNTA = @IDPERGUNTA AND IDMODERADOR =  @IDMODERADOR

          --excluir respostas
     UPDATE TB_RESPOSTA SET BLOQUEIO_RESPOSTA = 'S', 
         DATA_EXCLUIR = CONVERT(VARCHAR, GETDATE(), 20),
         DESCRICAO_BLOQUEIO = 'Excluido pelo moderador'  
         WHERE IDPERGUNTA = @IDPERGUNTA  

    END

    --pergunta aberta pelo usuário 
   IF ( @EXCLUIRPERGUNTA = 'N')
    BEGIN
      --pergunta aberta pelo usuário 
     UPDATE TB_PERGUNTA  SET  EXCLUIR_PERGUNTA = 'N', 
            DESCRICAO_BLOQUEIO = 'Excluido pelo usuário' , 
            DATA_EXCLUIR = null
            WHERE IDPERGUNTA = @IDPERGUNTA 
            AND IDUSUARIO =  @IDUSUARIO  

       --abre todas as respostas    
     UPDATE TB_RESPOSTA SET BLOQUEIO_RESPOSTA = 'N',
           DATA_EXCLUIR =null,
           DESCRICAO_BLOQUEIO = null 
           WHERE IDPERGUNTA = @IDPERGUNTA          
    END

    --pergunta aberta pelo moderador
   IF ( @BLOQUEIOPERGUNTA = 'N')
    BEGIN
     --abre a pergunta pelo moderador
     UPDATE TB_PERGUNTA  SET BLOQUEIO_PERGUNTA = 'N', 
            DESCRICAO_BLOQUEIO =null, 
            DATA_EXCLUIR =null
            WHERE IDPERGUNTA = @IDPERGUNTA 
            AND IDMODERADOR =  @IDMODERADOR

      --abre todas as respostas pelo moderador
     UPDATE TB_RESPOSTA SET BLOQUEIO_RESPOSTA = 'N', 
           DATA_EXCLUIR = null, 
           DESCRICAO_BLOQUEIO =null  
           WHERE IDPERGUNTA = @IDPERGUNTA  
           AND IDMODERADOR =  @IDMODERADOR  
    END

END

Answer 1

Notice that you are generating a recursive with Trigger, see:

Example the @LOCK variable is like "S", in this case you will generate an update again in the table thus activating Trigger again, this may cause problems, you will need to use the TRIGGER_NESTLEVEL command, which checks how many times Trigger has been run by herself, follows example to her Trigger:

   DECLARE @EXCLUIRPERGUNTA CHAR
   DECLARE @BLOQUEIOPERGUNTA CHAR
   DECLARE @IDPERGUNTA INT
   DECLARE @IDUSUARIO INT
   DECLARE @IDMODERADOR INT
   --Caso a trigger esteja chamando a si mesma a execução é interrompida com o comando RETURN
   IF ( SELECT TRIGGER_NESTLEVEL ( ) ) >= 2 
     return

   --se o excluirpergunta receber (S) então o usuário está bloqueando a pergunta se (N) está abrindo a pergunta