Hello,
Thanks for reply.
I always get help/correct solution from this nibble forum.
But my question is related to select criteria
Is there any restriction on String Length in select criteria
as it works for 'e2003412DC03011808176518' and same query does not work for
'e006'
FOR TRIGGERS
Your reply
For some reason, WHEN clause doesn't like a string value as a condition.
Taking a step back
But i can not take step back
I had truncated my table part and Trigger part just to get answer for above
question.
But if it is drawback of Sqlite specifically for Trigger then
I will find some another solution
Please ignor the UPDATE statement but try to focus on my question for Select
criteria with
'e2003412DC03011808176518' and 'e006' makes difference.
If required i will give my full details of table and trigger.
Thanks
Ram Mandavkar
Igor Tandetnik wrote:
>
> 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
>
>
--
View this message in context:
http://old.nabble.com/Trigger-Not-working-for-SELECT-criteria-tp27025539p27046843.html
Sent from the SQLite mailing list archive at Nabble.com.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
