Ram Mandavkar wrote:
> CREATE TABLE "READMODE" ("EPC" VARCHAR(50) COLLATE NOCASE ,"COUNT"
> INTEGER,"STATUS" VARCHAR)
> With 2 records
> 'e006',0,'0'
> 'e2003412DC03011808176518',0,'0'
>
> //
> --------------------------------------------------------------------------------------
> Trigger on ReadMode Table
> // --------------- //
> CREATE TRIGGER "main"."TR_MULTIREAD_AFTER" AFTER INSERT ON READMODE
> WHEN( SELECT EPC AS a FROM READMODE where EPC=NEW.EPC AND COUNT=0
> collate nocase )
>
> BEGIN
> UPDATE READMODE SET STATUS='1';
> END
> //
> --------------------------------------------------------------------------------------
> If i execute this query it works fine
> INSERT INTO READMODE ( EPC,COUNT,STATUS ) VALUES
> ('e2003412DC03011808176518',0,'0')
>
> If i execute this query it does not work ( UPDATE statement does not get
> execute )
> INSERT INTO READMODE ( EPC,COUNT,STATUS ) VALUES ('e006',0,'0')
For me, your trigger doesn't run regardless of which values are inserted.
Change it to
CREATE TRIGGER "main"."TR_MULTIREAD_AFTER" AFTER INSERT ON READMODE
WHEN exists (SELECT EPC AS a FROM READMODE where EPC=NEW.EPC AND COUNT=0)
BEGIN
UPDATE READMODE SET STATUS='1';
END
For some reason, WHEN clause doesn't like a string value as a condition.
Taking a step back - what exactly is your trigger supposed to achieve? It
doesn't make any sense to me. For one thing, you are updating all rows in
READMODE, not just the one that was inserted. If the goal is to make sure that,
whenever a new record is inserted with count=0, its status is set to '1', then
you want something like this:
CREATE TRIGGER TR_MULTIREAD_AFTER AFTER INSERT ON READMODE
WHEN new.COUNT = 0
BEGIN
UPDATE READMODE SET STATUS='1' where rowid=new.rowid;
END
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
