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