Parser bug in combination with triggers (sqljet 1.1.18)

[GoogleCodeExporter]

It seems that there is a bug in the parser (or the grammar) which cause an 
error when creating a special kind of trigger. The trigger looks like this:

"CREATE TRIGGER \"trgDataDel\" AFTER DELETE ON \"tblData\" BEGIN UPDATE 
\"tblStat\" SET \"value\" = ((SELECT \"value\" FROM \"tblStat\" WHERE 
\"key\"='NR') - 1) WHERE \"key\"='NR'; END;"

The tables which I am referencing already exist and the tblStat table also has 
one entry with key = 'NR' and an integer value set for column "value".

If I entered the queries in an SQLite browser found on the web it all works out 
fine. Even the trigger does what I expected. However, when using this with 
sqljet in the current version (1.1.18) it crashes with a SqlJetParserException.

I'm using sqljet 1.1.18 on a Windows 7 Enterprise 64-bit machine.

Attached you can find a short example in which I have tried to create a trigger 
(trgDataDel) which decrements a value in a statistics table (tblStat) each time 
a record is deleted from another table (tblData).

If you would like to find out WHY I am doing this, please check:
https://groups.google.com/forum/#!topic/sqljet/bZqa1G7Wp8Y

When reproducing this behaviour with the example attached in this report, make 
sure the location DB_FILE_NAME (constant) does really exist. It is set to drive 
D:\\ a.t.m.

Original issue reported on code.google.com by matze.gs...@cable.vol.at on 25 Nov 2013 at 3:10

Attachments:

• TriggerBug.java

[GoogleCodeExporter]

Original comment by sergey.s...@gmail.com on 25 Nov 2013 at 8:41

• Changed state: Accepted

[GoogleCodeExporter]

It seems that trigger do not work at all. Creating a much simpler trigger works:

private static final String TRG_DATA_DEL = "CREATE TRIGGER \"trgDataDel\"" +
        " AFTER DELETE ON \"tblData\" BEGIN" +
            " UPDATE \"tblStat\"" +
                " SET value=5;" +
        " END;";

This means, that it does not run into the bug described above. However, the 
trigger is never "executed" when deleting from tblData. Should be a separate 
issue I guess. But I'm running out of time right now. sry

Original comment by matze.gs...@cable.vol.at on 26 Nov 2013 at 12:15

[GoogleCodeExporter]

Yes, triggers don't work in SQLJet - they are supported in DB schema just for 
compability with native SQLite. So far SQLJet doesn't support SQL at all in 
run-time - only for database schema definition. It means that if you want use 
SQLJet then you should handle all data logic in code through API. So when you 
delete data in table then you also should update data in other table - you 
should write this code in Java. Yes, of course, it is limitation of SQLJet. 
Maybe in future it will be resolved in next major version of SQLJet.

But there definitely is issue with parsing SQL for triggers, which you found.

Original comment by sergey.s...@gmail.com on 26 Nov 2013 at 2:07