On 11/10/2013 12:54 AM, Igor Korot wrote:
CREATE TRIGGER playersinleague_insert AFTER INSERT on playersinleague
BEGIN
UPDATE playersinleague SET current_rank = 1+ (select max(
current_rank ) from playersinleague WHERE id = new.id), original_rank
= current_rank WHERE id = new.id AND current_rank IS NULL;
END;
This trigger makes no sense at all. It says: when a row is inserted with
current_rank of NULL, take the value of current_rank *from that row*
(which is, of course, NULL), add 1 to it (which produces NULL), and
write it back to current_rank (which thus remains NULL). The whole thing
is a very elaborate no-op.
I suspect you want to drop "WHERE id = new.id" part from the subquery.
Also, "original_rank = current_rank" uses the current value of
current_rank before the UPDATE (that is, NULL), not the new value.
SELECT * FROM playersinleague WHERE id=1 AND playerid=1;
1|1|1|27|42.0|42.0|0|0|||0
I didn't specify current_rank on the INSERT query which means the
field shuold be NULL.
Yes it is. Which is what the SELECT statement shows. Which part is
surprising?
Also will the UPDATE in trigger do the right thing and correctly
update the original_rank?
That rather depends on what value you deem to be the correct one. You've
never explained the desired outcome of all this choreography.
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
