SQLite triggers in Android ?

Question

Hi all

I want to forece a foreign key constarint on a table in an Android application.

I've searched that this can be done by using triggers:

I did it like this:

db.execSQL("CREATE TRIGGER dept_id_trigger22+" +
                " AFTER INSERT "+
                " OF EmployeeName ON Employees"+
                " BEGIN"+
                                     //Condition
                " RAISE(ABORT,'error') END;");

but no error was raised and the illegal values are inserted.

what is wrong with this ?

Answer 1

I discovered that the SQLite version used does not support foreign keys - so I expect that triggers are not supported, too.

Answer 2

I don't expect any votes for this answer, just to let you know:

You could use another database, for example the H2 database. Disclaimer: I'm the main author of H2.

There are some disadvantages: some (not all) operations are slower, for example opening and closing a database. The jar file is relatively big (about 1 MB). You would have to use the JDBC API.

But the advantage is: H2 supports using triggers, constraints, and so on.

Answer 3

Ok I got it

SQLite on Androi supports triggers the correct syntax is

db.execSQL("CREATE TRIGGER dept_id_trigger22" + 
                " AFTER INSERT "+ 
                "ON Employees"+ 
                " BEGIN"+ 
                                     //Condition 
                " SELECT RAISE(ABORT,'error'); END;"); 

I forgot to add semicolon after the raise statement.

this does not execute the statement but it does not throw an exception. still will search for how to throw exceptions

thanks