Como posso Construir esta Trigger?

Question

Olá pessoal,

Preciso fazer uma trigger no Insert e no Update onde eu precise testar valores para montar o peso a ser utilizado para calcular notas de 5 disciplinas, mas da forma que estou fazendo está dando erro:

CREATE TRIGGER [dbo.TBNOTAS].TBNOTAS_tri ON [dbo.TBNOTAS].
WITH EXECUTE AS CALLER
FOR INSERT
AS
BEGIN
  /* Trigger body */
  DECLARE 	@ID int,
  			@P decimal,
            @M decimal,
            @F decimal,
            @Q decimal,
            @B decimal,
            @T decimal,
            @PESO int;
  
  SELECT	@ID = ID,
  			@P = PORTUGUES,
            @M = MATEMATICA,
  			@F = FISICA,
            @Q = QUIMICA,
            @B = BIOLOGIA,
            @PESO = CASE WHEN PORTUGUES IS NULL THEN 0 ELSE 1 END +
            CASE WHEN MATEMATICA IS NULL THEN 0 ELSE 1 END +
            CASE WHEN FISICA IS NULL THEN 0 ELSE 1 END +
            CASE WHEN QUIMICA IS NULL THEN 0 ELSE 1 END +
            CASE WHEN BIOLOGIA IS NULL THEN 0 ELSE 1 END
  FROM INSERTED;
  
  IF @PESO > 0
  BEGIN
    UPDATE TBNOTAS SET
           TOTAL = ((@P + @M + @F + @Q + @B) / @PESO)
    WHERE ID = @ID;
  ELSE
  	BEGIN
    	UPDATE TBNOTAS SET
               TOTAL = NULL
        WHERE ID = @ID;
    END;
  

END
GO

Como posso resolver isso?

Grato,

Ilano.

Answer 1

Consegui criar, porém, o total retorna NUL, mesmo passando valores para os outros campos.

CREATE TRIGGER dbo.TBNOTAS_tri2 ON dbo.TBNOTAS
WITH EXECUTE AS CALLER
FOR INSERT
AS
BEGIN
  /* Trigger body */
  DECLARE 	@ID int,
  			@P decimal,
            @M decimal,
            @F decimal,
            @Q decimal,
            @B decimal,
            @T decimal,
            @PESO int;
  
  SELECT	@ID = ID,
  			@P = PORTUGUES,
            @M = MATEMATICA,
  			@F = FISICA,
            @Q = QUIMICA,
            @B = BIOLOGIA,
            @PESO = CASE WHEN @P IS NULL THEN 0 ELSE 1 END +
            CASE WHEN @M IS NULL THEN 0 ELSE 1 END +
            CASE WHEN @F IS NULL THEN 0 ELSE 1 END +
            CASE WHEN @Q IS NULL THEN 0 ELSE 1 END +
            CASE WHEN @B IS NULL THEN 0 ELSE 1 END
  FROM INSERTED;
  
  IF @PESO > 0
  BEGIN
    UPDATE TBNOTAS SET
           TOTAL = ((@P + @M + @F + @Q + @B) / @PESO)
    WHERE ID = @ID
  END
  ELSE
  	IF @PESO = 0
  	BEGIN
    	UPDATE TBNOTAS SET
               TOTAL = NULL
        WHERE ID = @ID
    END;
  

END
GO

O que está faltando?

Answer 2

Alterei o teste da variável @PESO para:

@PESO = CASE WHEN PORTUGUES IS NULL THEN 0 ELSE 1 END +
            CASE WHEN MATEMATICA IS NULL THEN 0 ELSE 1 END +
            CASE WHEN FISICA IS NULL THEN 0 ELSE 1 END +
            CASE WHEN QUIMICA IS NULL THEN 0 ELSE 1 END +
            CASE WHEN BIOLOGIA IS NULL THEN 0 ELSE 1 END

Mas só dá certo se todos os campos possuir valores, mas quero que conte apenas aqueles que possuir algum valor, os que são nulos quero considerar 0 na soma.

Falta pouco, esse pouco é que lasca! rrrsssss...

Answer 3

Boa tarde,

Acho que o problema está na soma das notas.

Experimente fazer uns testes dessa forma considerando também a possibilidade de inserir ou atualizar mais de uma linha com um só Insert ou Update:

CREATE TRIGGER dbo.TBNOTAS_tri2 ON dbo.TBNOTAS
WITH EXECUTE AS CALLER
FOR INSERT
AS
BEGIN

with CTE_Pesos as
(
    SELECT
        ID,
        coalesce(PORTUGUES, 0) as PORTUGUES,
        coalesce(MATEMATICA, 0) as MATEMATICA,
        coalesce(FISICA, 0) as FISICA,
        coalesce(QUIMICA, 0) as QUIMICA,
        coalesce(BIOLOGIA, 0) as BIOLOGIA,
        CASE WHEN PORTUGUES IS NULL THEN 0 ELSE 1 END +
        CASE WHEN MATEMATICA IS NULL THEN 0 ELSE 1 END +
        CASE WHEN FISICA IS NULL THEN 0 ELSE 1 END +
        CASE WHEN QUIMICA IS NULL THEN 0 ELSE 1 END +
        CASE WHEN BIOLOGIA IS NULL THEN 0 ELSE 1 END as PESO
    FROM INSERTED
)

update n
set 
    n.TOTAL = 
        case when p.PESO = 0 
            then null 
            else (p.PORTUGUES + p.MATEMATICA + p.FISICA + p.QUIMICA + p.BILOGIA) / p.PESO
        end
from TBNOTAS as n
inner join CTE_Pesos as p
    on p.ID = n.ID;

END
GO

Espero que ajude

---

Assinatura: http://www.imoveisemexposicao.com.br

Answer 4

Consegui!!!!!!!!!!!!!!

BEGIN
  /* Trigger body */
  DECLARE 	@ID int,
  			@P decimal(10,2),
            @M decimal(10,2),
            @F decimal(10,2),
            @Q decimal(10,2),
            @B decimal(10,2),
            @T decimal(10,2),
            @PESO int;
  
  SELECT	@ID = ID,
  			@P = PORTUGUES,
            @M = MATEMATICA,
  			@F = FISICA,
            @Q = QUIMICA,
            @B = BIOLOGIA,
            @PESO = (CASE PORTUGUES WHEN NULL THEN 0 ELSE 1 END) +
                    (CASE MATEMATICA WHEN NULL THEN 0 ELSE 1 END) +
                    (CASE FISICA WHEN NULL THEN 0 ELSE 1 END) +
                    (CASE QUIMICA WHEN NULL THEN 0 ELSE 1 END) +
                    (CASE BIOLOGIA WHEN NULL THEN 0 ELSE 1 END)
  FROM INSERTED;
  
  IF @PESO > 0
  BEGIN
    UPDATE TBNOTAS SET
           TOTAL = ((ISNULL(@P, 0) +
           	    ISNULL(@M, 0) +
                    ISNULL(@F, 0) +
                    ISNULL(@Q, 0) +
                    ISNULL(@B, 0)) / @PESO)
    WHERE ID = @ID;
  END
  ELSE
  	IF @PESO = 0
  	BEGIN
    	UPDATE TBNOTAS SET
               TOTAL = NULL
        WHERE ID = @ID;
    END;
  

END