Question:
I'm trying to create this trigger
in SQLite
, but I don't know if it's possible.
I have a Remedios
and a Manutenção
tab. I want that, when there is an insertion in the Manutenção
table, it does an update depending on the condition in Case
, but it is giving an error in Update
.
CREATE TRIGGER TGmovimentacao
AFTER INSERT
ON manutencao
for each row
BEGIN
Select Case
When ( NEW.tpMov = 'S' )
then UPDATE Remedio set Remedio.RemedioDose = Remedio.RemedioDose - NEW.qtdDose where Remedio.idRemedio = NEW.idManutencao
END;
END;
Answer:
Your trigger has some syntax errors. Here is an alternative
CREATE TRIGGER TGmovimentacao AFTER INSERT ON manutencao
WHEN NEW.tpMov = 'S'
BEGIN
UPDATE Remedio
SET Remedio.RemedioDose = Remedio.RemedioDose - NEW.qtdDose
WHERE Remedio.idRemedio = NEW.idManutencao
END;
The FOR EACH ROW
statement is optional as SQLite only supports FOR EACH ROW
triggers and not FOR EACH STATEMENT
triggers.
Edit: This version should update the RemedioDose value according to tpMov.
CREATE TRIGGER TGmovimentacao AFTER INSERT ON manutencao
BEGIN
UPDATE Remedio
SET Remedio.RemedioDose = Remedio.RemedioDose +
CASE
WHEN NEW.tpMov = 'S' THEN NEW.qtdDose * (-1)
WHEN NEW.tpMov = 'E' THEN NEW.qtdDose
ELSE
END
WHERE Remedio.idRemedio = NEW.idManutencao
END;