SQLite trigger error

Question

SQLite trigger error

Navigation

#1 by (2 votes)

3

I'm trying to create this trigger in SQLite , but I do not know if it's possible.

I have a tabea Remedios and a Manutenção . I want, when I have an insert in the Manutenção table, it does an update depending on the condition in Case , but it is giving 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;

android sqlite

asked by anonymous 20.11.2015 / 18:28

1 answer

How to send data from a php loop to an input using JS Check if an element of an array is contained in each element of another array with php

score 2 · Accepted Answer

Your trigger has some syntax errors. Here's 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 since 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;