Kees,
Thank you very much for quick prototype. I will use single quotes from now on.
It turns out Igor was right.
I had another trigger
CREATE TRIGGER insert_players_timeStamp AFTER INSERT ON players
BEGIN
UPDATE players SET create_ts = DATETIME('NOW', 'localtime')
WHERE rowid = new.rowid;
END;
it ticked another update trigger. I found workaround by adding UPDATE OF
clause for a specific field
CREATE TRIGGER validate_players_update_role BEFORE UPDATE OF role ON players
........
Now I even understand why it works.
Thanks a lot,
-B
--- On Tue, 6/2/09, Kees Nuyt <[email protected]> wrote:
> From: Kees Nuyt <[email protected]>
> Subject: Re: [sqlite] Before Update trigger question
> To: [email protected]
> Date: Tuesday, June 2, 2009, 2:35 PM
> 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
> > DROP TRIGGER validate_players_update_role;
CREATE TRIGGER validate_players_update_role 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(ROLLBACK, "1002: Can not change player to this role at
the table");
END;e
>
>
> 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
>
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
