mercredi 22 avril 2015

SQLite Trigger to increment values conditionally

I have a column that I would like to increment based on newly inserted/updated records, but what I've got so far is not working.

So my table ProcedureCommands has a column Sequence which is an integer. Whenever a new record is created with the Sequence value 5 for example, I would like all records that have the same ProcedureId and Sequence value >= 5 to be incremented by 1.

Table Script

CREATE TABLE ProcedureCommands (
ProcedureCommandId INTEGER PRIMARY KEY AUTOINCREMENT
                           NOT NULL,
ProcedureId        INTEGER NOT NULL,
Sequence           INTEGER NOT NULL);

INSERT INTO ProcedureCommands(ProcedureId, Sequence) VALUES(1, 3);
INSERT INTO ProcedureCommands(ProcedureId, Sequence) VALUES(1, 4);
INSERT INTO ProcedureCommands(ProcedureId, Sequence) VALUES(1, 5);
INSERT INTO ProcedureCommands(ProcedureId, Sequence) VALUES(1, 6);
INSERT INTO ProcedureCommands(ProcedureId, Sequence) VALUES(2, 4);
INSERT INTO ProcedureCommands(ProcedureId, Sequence) VALUES(2, 5);

Trigger Script

CREATE TRIGGER Update_Sequence UPDATE OF Sequence ON ProcedureCommands FOR EACH ROW
BEGIN
UPDATE ProcedureCommands
   SET Sequence = CASE WHEN Sequence >= new.Sequence THEN Sequence = Sequence + 1 ELSE Sequence END
 WHERE ProcedureId = new.ProcedureId;
END;

Unfortunately the Trigger as written does not work. How can I conditionally increment the field after inserts/updates?

Aucun commentaire:

Enregistrer un commentaire