When firing an Update Trigger, one of the fields only refreshes after a second Update

1

I created a trigger to report the fine on the return delay of rented movies, however, when triggering the trigger once (giving an UPDATE), the "MultaTotal" field does not update (only it). But when the trigger is triggered a second time, the field informed is updated normally. Does anyone know how to solve it, if it is a problem, or is it something normal for the case presented?

TRIGGER:

instead of update
as

declare @CodLoc int
declare @FilmeLoc int
declare @Multa float

select @CodLoc = CodLoc, @FilmeLoc = FilmeLoc, @Multa = Atraso * MultaDia

from inserted

UPDATE Locacao
SET Atraso = DATEDIFF(DAY,DtAluguel, GETDATE()), DtEntrega = GETDATE(), Situacao = 'Devolvido', MultaDia = (SELECT MultaDia FROM Filmes WHERE CodFilme = @FilmeLoc), MultaTotal = @Multa
where CodLoc = @CodLoc

update Filmes
set Disponibilidade = 'Disponivel'
where CodFilme = @FilmeLoc

And the following table:

TABLE Locacao
(
CodLoc INT IDENTITY (1,1) NOT NULL,
ClienteLoc INT FOREIGN KEY REFERENCES Clientes(CodCliente) NOT NULL,
FilmeLoc INT FOREIGN KEY REFERENCES Filmes(CodFilme) NOT NULL,
DtAluguel DATE,
DtParaEntrega DATE,
DtEntrega DATE,
Atraso INT,
Situacao VARCHAR(9), /* Alugado ou Devolvido */
MultaDia FLOAT,
MultaTotal FLOAT,
PRIMARY KEY (CodLoc)
)

However, when I do an Update, the FullTotal field only refreshes after a second Update:

Example:

    insert into Locacao (ClienteLoc, FilmeLoc, DtAluguel, DtParaEntrega, Situacao, MultaDia)
    values (2, 1, GETDATE(), DATEADD(DAY, 3, GETDATE()), 'Alugado', 2)
    SELECT * FROM Locacao

CodLoc      ClienteLoc  FilmeLoc    DtAluguel  DtParaEntrega DtEntrega  Atraso      Situacao  MultaDia               MultaTotal
----------- ----------- ----------- ---------- ------------- ---------- ----------- --------- ---------------------- ----------------------
3           2           1           2018-12-09 2018-12-12    NULL       NULL        Alugado   2                      NULL

Update 1:

update Locacao
set DtEntrega = GETDATE()
where CodLoc = 3
SELECT * FROM Locacao

CodLoc      ClienteLoc  FilmeLoc    DtAluguel  DtParaEntrega DtEntrega  Atraso      Situacao  MultaDia               MultaTotal
----------- ----------- ----------- ---------- ------------- ---------- ----------- --------- ---------------------- ----------------------
3           2           1           2018-12-09 2018-12-12    2018-12-09 0           Devolvido 2                      NULL

Update 2:

update Locacao
set DtEntrega = GETDATE()
where CodLoc = 3
SELECT * FROM Locacao

CodLoc      ClienteLoc  FilmeLoc    DtAluguel  DtParaEntrega DtEntrega  Atraso      Situacao  MultaDia               MultaTotal
----------- ----------- ----------- ---------- ------------- ---------- ----------- --------- ---------------------- ----------------------
3           2           1           2018-12-09 2018-12-12    2018-12-09 0           Devolvido 2                       0
    
asked by anonymous 09.12.2018 / 16:27

1 answer

4

Trigger procedures are more difficult to program in SQL Server than in other managers, because of the schema that is used internally in SQL Server.

In general, when triggering trigger procedures for UPDATE events, you must keep in mind that it will be triggered for any and all changes to any column (s) in the table. For example, if there is a change in the expected return date (wrong entry correction), the trigger procedure you created handles the change as if it were a return! Did you catch the clue you fell into?

In time, it is not reliable to use columns of data type float to store monetary values; I suggest that you change the declaration of the columns MultaDia and MultaTotal to data type numeric .

There are several points to review in the trigger procedure.

(1) The fine is calculated without the delay in days being calculated:

@Multa = Atraso * MultaDia

As Atraso is missing information (NULL) on the first run, then the value of @Multa will be NULL. Incidentally, this is the reason for the irregular functioning that he realized.

(2) The delay calculation should take into account the delay in relation to the expected delivery date and not in relation to the rental date.

Atraso = DATEDIFF(DAY,DtAluguel, GETDATE())

In addition, it should only be calculated if you are really late. Otherwise, the value will be negative if the movie is returned before the deadline ...

(3) At the time of inclusion, the amount of the fine per day was recorded; so it does not make sense to get this value again at the time of the upgrade:

MultaDia = (SELECT MultaDia FROM Filmes WHERE CodFilme = @FilmeLoc)

mainly that the total amount of the fine has already been calculated previously:

@Multa = Atraso * MultaDia

And there are other points that need review. Incidentally, the procedure would have to be rewritten from zero . However, I suggest you implement the fine calculation in the application itself; is much simpler.

In the article " Traps in the trigger procedures programming are related some of the clutches.

    
10.12.2018 / 12:21