Hi, ALL,
CREATE TABLE playersinleague(
id integer
playerid integer
ishitter char
age integer
value double
currvalue double
draft boolean
isnew char(1)
current_rank integer
original_rank integer
deleted integer
foreign key(id) references leagues(id)
foreign key(playerid) references players(playerid)
);
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;
or do it this way:
DROP TRIGGER playersinleague_insert;
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 rowid = new.rowid;
END;
After that:
INSERT INTO playersinleague(id,playerid,ishitter,age,value,currvalue,dra
ft,isnew,deleted) SELECT 1, players.playerid, players.ishitter, players.age, pla
yers.value, players.value, 0, "0", 0 FROM players WHERE (teamid >= 1 AND teamid
<= 15) OR (teamid >= 16 AND teamid <= 30) ORDER BY rank;
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.
I think I am doing something wrong but I don't see what.
Could someone please help?
Also will the UPDATE in trigger do the right thing and correctly
update the original_rank?
Thank you.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
