On 10/31/2013 11:26 PM, Keith Medcalf wrote:
https://www.sqlite.org/lang_createtrigger.html
CREATE TRIGGER foo AFTER INSERT ON table
BEGIN
UPDATE table
SET field1 = field1 + 1
WHERE new.field1 IS NOT NULL
AND field1 > new.field1;
UPDATE table
SET field1 = (SELECT MAX(field1)+1 FROM table)
WHERE rowid = new.rowid AND new.field1 IS NULL;
END;
Almost, but not quite. The OP wants it "so if new record have field1 as
5, so the old record with field1 == 5 will have field1 = 6". The first
UPDATE statement should be like this:
UPDATE mytable set field1 = field1 + 1
WHERE new.field1 IS NOT NULL and field1 >= new.field1 and rowid !=
new.rowid;
Alternatively, the IS NOT NULL check could be moved into WHEN clause of
the trigger itself. This means two triggers, with one statement each:
create trigger foo_insert_null after insert on foo WHEN new.field1 is null
begin
update foo set field1 = 1 + (select max(field1) from foo) where rowid
= new.rowid;
end;
create trigger foo_insert_not_null after insert on foo WHEN new.field1
is NOT null
begin
update foo set field1 = field1 + 1 where field1 >= new.field1 and
rowid != new.rowid;
end;
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
