Tiberio, Sylvain <sylvain.tibe...@eads.com> wrote:
> So I would like to write something like that:
> CASE WHEN
> EXISTS( SELECT @max=suid_max, @min=roomIdMin FROM range
> WHERE 200 BETWEEN roomIdMin AND roomIdMax
> AND 400 BETWEEN roomIdMin AND roomIdMax)
> THEN
> DELETE FROM range WHERE roomidm...@min AND roomidm...@max;
> INSERT INTO range VALUES (1, @min, 199 );
> INSERT INTO range VALUES (1, 301 , @max );
> END
Things like this are best done in your application code, rather than a trigger.
SQLite in particular has a limited trigger language, nowhere near
Turing-complete (the way Transact-SQL or PL/SQL are).
However, if you absolutely insist, you can do something like this:
insert into range
select 1, roomIdMin, 199 from range
WHERE 200 BETWEEN roomIdMin AND roomIdMax
AND 400 BETWEEN roomIdMin AND roomIdMax;
insert into range
select 1, 301, roomIdMax from range
WHERE 200 BETWEEN roomIdMin AND roomIdMax
AND 400 BETWEEN roomIdMin AND roomIdMax;
delete from range where rowid =
(select rowid from range
WHERE 200 BETWEEN roomIdMin AND roomIdMax
AND 400 BETWEEN roomIdMin AND roomIdMax);
Note that if the condition doesn't hold (there is no row encompassing both 200
and 400), all three statements are simply no-ops.
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
