Hi,
Here is my database:
CREATE TABLE room (roomId INTEGER PRIMARY KEY,
roomName TEXT);
CREATE TABLE range(level INTEGER,
roomIdMin INTEGER,
roomIdMax INTEGER,
CHECK (roomIdMin<=roomIdMax) );
CREATE TRIGGER room_ins
BEFORE INSERT ON room
FOR EACH ROW BEGIN
SELECT RAISE(FAIL,'Level does not exist for this room')
WHERE (SELECT count(*)
FROM range
WHERE NEW.roomId BETWEEN range.roomIdMin AND range.roomIdMax)!=
1;
END
CREATE TRIGGER range_del BEFORE DELETE ON range FOR EACH ROW
BEGIN
SELECT RAISE(FAIL,'Room exist in this range')
WHERE (SELECT count(*)
FROM room
WHERE room.roomId BETWEEN OLD.roomIdMin AND OLD.roomIdMax)= 0;
END;
INSERT INTO range VALUES(1, 1, 3);
INSERT INTO range VALUES(2, 4, 6);
INSERT INTO room VALUES(1, 'AAAA');
INSERT INTO room VALUES(2, 'BBBB');
INSERT INTO room VALUES(3, 'CCCC');
INSERT INTO room VALUES(4, 'DDDD');
INSERT INTO room VALUES(5, 'EEEE');
INSERT INTO room VALUES(6, 'FFFF');
SELECT roomID, RoomName, level
FROM room, range
WHERE roomID BETWEEN range.roomIdMin AND range.roomIdMax;
| roomId | roomName | level |
+------------+------------+------------+
| 1 | AAAA | 1 |
| 2 | BBBB | 1 |
| 3 | CCCC | 1 |
| 4 | DDDD | 2 |
| 5 | EEEE | 2 |
| 6 | FFFF | 2 |
Trigger room_ins checks that a range exist each time I create a new room:
INSERT INTO room VALUES(7, 'GGGG') => ERROR: Level does not exist for this
room
Trigger range_del checks that no room exist in the range I try to delete.
But it doesn't work!
DELETE FROM range where level=1
SELECT roomID, RoomName, level
FROM room, range
WHERE roomID BETWEEN range.roomIdMin AND range.roomIdMax
| roomId | roomName | level |
+------------+------------+------------+
| 4 | DDDD | 2 |
| 5 | EEEE | 2 |
| 6 | FFFF | 2 |
When I EXPLAIN the DELETE statement I get:
EXPLAIN DELETE FROM range where level=1
| addr | opcode | p1 | p2 | p3 |
p4 | p5 | comment |
+------------+------------+------------+------------+------------+----------
--+------------+------------+
| 0 | Trace | 0 | 0 | 0 |
| 00 | <NULL> |
| 1 | Goto | 0 | 24 | 0 |
| 00 | <NULL> |
| 2 | Null | 0 | 1 | 0 |
| 00 | <NULL> |
| 3 | Integer | 1 | 3 | 0 |
| 00 | <NULL> |
| 4 | OpenRead | 0 | 3 | 0 |
1 | 00 | <NULL> |
| 5 | Rewind | 0 | 11 | 0 |
| 00 | <NULL> |
| 6 | Column | 0 | 0 | 4 |
| 00 | <NULL> |
| 7 | Ne | 3 | 10 | 4 |
collseq(BINARY) | 6c | <NULL> |
| 8 | Rowid | 0 | 2 | 0 |
| 00 | <NULL> |
| 9 | RowSetAdd | 1 | 2 | 0 |
| 00 | <NULL> |
| 10 | Next | 0 | 6 | 0 |
| 01 | <NULL> |
| 11 | Close | 0 | 0 | 0 |
| 00 | <NULL> |
| 12 | OpenWrite | 0 | 3 | 0 |
3 | 00 | <NULL> |
| 13 | RowSetRead | 1 | 22 | 2 |
| 00 | <NULL> |
| 14 | NotExists | 0 | 21 | 2 |
| 00 | <NULL> |
| 15 | Copy | 2 | 6 | 0 |
| 00 | <NULL> |
| 16 | Column | 0 | 1 | 8 |
| 00 | <NULL> |
| 17 | Column | 0 | 2 | 9 |
| 00 | <NULL> |
| 18 | Program | 6 | 21 | 10 |
program | 01 | <NULL> |
| 19 | NotExists | 0 | 21 | 2 |
| 00 | <NULL> |
| 20 | Delete | 0 | 1 | 0 |
range | 00 | <NULL> |
| 21 | Goto | 0 | 13 | 0 |
| 00 | <NULL> |
| 22 | Close | 0 | 0 | 0 |
| 00 | <NULL> |
| 23 | Halt | 0 | 0 | 0 |
| 00 | <NULL> |
| 24 | Transaction | 0 | 1 | 0 |
| 00 | <NULL> |
| 25 | VerifyCookie | 0 | 3 | 0 |
| 00 | <NULL> |
| 26 | TableLock | 0 | 3 | 1 |
range | 00 | <NULL> |
| 27 | TableLock | 0 | 2 | 0 |
room | 00 | <NULL> |
| 28 | Goto | 0 | 2 | 0 |
| 00 | <NULL> |
| 0 | Trace | 0 | 0 | 0 | --
TRIGGER range_del | 00 | <NULL> |
| 1 | Integer | 0 | 1 | 0 |
| 00 | <NULL> |
| 2 | Null | 0 | 3 | 0 |
| 00 | <NULL> |
| 3 | Integer | 1 | 4 | 0 |
| 00 | <NULL> |
| 4 | Null | 0 | 5 | 0 |
| 00 | <NULL> |
| 5 | Param | 2 | 6 | 0 |
| 00 | <NULL> |
| 6 | Param | 3 | 7 | 0 |
| 00 | <NULL> |
| 7 | OpenRead | 0 | 2 | 0 |
0 | 00 | <NULL> |
| 8 | SeekGe | 0 | 14 | 6 |
| 00 | <NULL> |
| 9 | SCopy | 7 | 9 | 0 |
| 00 | <NULL> |
| 10 | Rowid | 0 | 8 | 0 |
| 00 | <NULL> |
| 11 | Gt | 9 | 14 | 8 |
| 6b | <NULL> |
| 12 | AggStep | 0 | 0 | 5 |
count(0) | 00 | <NULL> |
| 13 | Next | 0 | 10 | 0 |
| 00 | <NULL> |
| 14 | Close | 0 | 0 | 0 |
| 00 | <NULL> |
| 15 | AggFinal | 5 | 0 | 0 |
count(0) | 00 | <NULL> |
| 16 | SCopy | 5 | 10 | 0 |
| 00 | <NULL> |
| 17 | Move | 10 | 3 | 1 |
| 00 | <NULL> |
| 18 | IfZero | 4 | 19 | -1 |
| 00 | <NULL> |
| 19 | Ne | 1 | 21 | 3 |
| 6a | <NULL> |
| 20 | Halt | 19 | 3 | 0 | Room
exist in this range | 00 | <NULL> |
| 21 | Halt | 0 | 0 | 0 |
| 00 | <NULL> |
The trigger seams to be called and raised an error. Why the DELETE statement
doesn't fail?
Sylvain
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
