android – SQLite trigger error

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;