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

Question

I created a Rigger to inform the fine about the delay of returning rented movies, however, when firing the Rigger once (giving an UPDATE), the field "Total Fine" does not update (only it). But when Rigger fires a second time, the reported field is updated normally. Does anyone know how to solve, if it is a problem, or if it is 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 Total Fine field only updates 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

Answer 1

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

In general, when programming trigger procedure for UPDATE events it is necessary to keep in mind that it will be triggered for any and all changes in any column(s) of the table. For example, if there is a change in the expected return date (wrong input correction), the trigger procedure you created treats the change as if it were a return! You know the trap you fell into?

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

There are several points to be reviewed in the trigger procedure.

(1) The amount of the fine is calculated without the delay in days having been calculated:

@Multa = Atraso * MultaDia

Like Atraso is without information (NULL) in the first run, so the value of @Multa will be NULL. Incidentally, this is the reason for the irregular functioning that understood.

(2) The calculation of the delay should consider the delay in relation to the expected delivery date and not in relation to the lease date.

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

Also, it should only be calculated if it is actually overdue. Otherwise, the value will be negative if the film is returned before the deadline...

(3) At the time of inclusion the amount of the fine per day was recorded; so it makes no sense to get this value again at the time of update:

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 revision. In fact, the procedure would have to be rewritten from scratch. However, I suggest you implement the fine calculation in the application itself; it’s much simpler.

Including in the article "Traps in trigger procedure programming (Trigger)"are related some of the traps.