我有一列,我想根据新插入/更新的记录递增,但到目前为止我得到的不起作用。
所以我的表ProcedureCommands
有一个列Sequence
它是一个整数。例如,每当使用Sequence
值 5 创建新记录时,我希望具有相同ProcedureId
和值 Sequence
>= 5 的所有记录都递增 1。
表脚本
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);
触发器脚本
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;
不幸的是,编写的触发器不起作用。如何在插入/更新后有条件地增加字段?
处理新记录的触发器是 INSERT 触发器。
Sequence = Sequence + 1
比较两个值,并返回比较结果。在这种情况下,这两个值永远不能相等,因此结果始终为零。
并且您不想更新插入的行本身,因此必须使用 >
而不是 >=
.
如果不想更新某些行,将筛选器放入 WHERE 子句会更容易:
CREATE TRIGGER Update_Sequence
AFTER INSERT ON ProcedureCommands
FOR EACH ROW
BEGIN
UPDATE ProcedureCommands
SET Sequence = Sequence + 1
WHERE ProcedureId = new.ProcedureId
AND Sequence > new.Sequence;
END;