On Tue, 2 Jun 2009 09:38:18 -0700 (PDT), Boris Ioffe
<[email protected]> wrote:
>
>Hello Gang,
>This is my first question on this mail list. I noticed that BEFORE UPDATE
>trigger goes off even for insert statements.
>
>My example:
>CREATE TRIGGER validate_players_update BEFORE UPDATE ON players
> WHEN (new.role in (1,2) and
> (select count(*) from players where table_group_id =
> new.table_group_id
> and table_id = new.table_id
> and role = new.role))
>
> BEGIN
> SELECT RAISE(FAIL, "1002: Can not sit player at this role at the
> table");
> END;
>
>
>INSERT INTO players (device_id,table_group_id,table_id,role ) VALUES((select
>device_id from registrations where mesg_token ="aaaaaa"), 1, 2 , 2);
>2009-06-02 10:43:36,086 SQLEngine->pysqlite2.dbapi2.IntegrityError
>Traceback (most recent call last):
> File "SQLEngine.py", line 39, in executeUpdate
> self.cur.execute(SQL, args)
>IntegrityError: 1002: Can not sit player at this role at the table
>
>
>Can someone please shed a light on this issue?
>Thanks,
>Boris
I couldn't reproduce your problem. See code below.
(By the way, you really shouldn't use double quotes for
string literals!)
The validate_players_update never fires.
The validate_players_insert does.
If I comment the validate_players_insert trigger out, no
trigger fires.
Please provide a script that demonstrates the problem.
It should run against the command line tool, like the SQL
below, so things aren't obfuscated by a wrapper.
sqlite_version():3.6.13 -- yeah, I should update.
CREATE TABLE players (
table_group_id INTEGER,
table_id INTEGER,
device_id INTEGER,
role INTEGER
);
CREATE TABLE registrations (
mesg_token TEXT,
device_id INTEGER
);
CREATE TRIGGER validate_players_insert
BEFORE INSERT ON players
WHEN (new.role IN (1,2) AND
(SELECT count(*) FROM players
WHERE table_group_id = new.table_group_id
AND table_id = new.table_id
AND role = new.role))
BEGIN
SELECT RAISE(FAIL, '1001: Insert');
END;
CREATE TRIGGER validate_players_update
BEFORE UPDATE ON players
WHEN (new.role IN (1,2) AND
(SELECT count(*) FROM players
WHERE table_group_id = new.table_group_id
AND table_id = new.table_id
AND role = new.role))
BEGIN
SELECT RAISE(FAIL, '1002: Update');
END;
INSERT INTO registrations VALUES ('aaaaaa',1);
INSERT INTO registrations VALUES ('bbbbbb',2);
INSERT INTO players (device_id,table_group_id,table_id,role
) VALUES (
(SELECT device_id FROM registrations
WHERE mesg_token = 'bbbbbb')
, 1, 2, 2);
INSERT INTO players (device_id,table_group_id,table_id,role
) VALUES (
(SELECT device_id FROM registrations
WHERE mesg_token = 'aaaaaa')
, 1, 2, 2);
SQL error near line 38: 1001: Insert
--
( Kees Nuyt
)
c[_]
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
