SQLite triggers in Android?

I want to force a foreign key constarint on a table in an An开发者_运维百科droid 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 ?

---

Ok I got it

Android supports SQLite 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

---

Foreign keys are only supported on Android on Froyo (2.2) or newer, for previous versions you can include them but SQLite ignores them. All Android versions of SQLite support triggers to produce the same effect though.

Newer versions of SQLite (for your PC) has a command called "genfkey" that will analyze your SQLite database (which has foreign keys in it) and produce the equivalent triggers. This way you can design your tables with foreign key constraints while also supporting all versions of the OS.

On Windows, open the SQLite command line tool with your database file as a parameter:

sqlite3 mydatabase.db
.genfkey --exec

This will generate triggers for all of your key constraints.

---

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.

---

To delete Last 50 rows when count is greater than 100

sqliteDB.execSQL("CREATE TRIGGER IF NOT EXISTS delete_trigger
    AFTER INSERT ON table1 
    WHEN (SELECT COUNT(*) FROM table1) > 50 " +
    BEGIN
       delete From table1 where id not in(select id from table1 order by id desc limit 100;
    END;"
);

---

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