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