SQLite triggers in Android?

Question

I want to force 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 ?

I don't have the code here, but a basic trigger worked for me on SQLite and (according to my unit tests) it worked on all Android versions. — Christopher Orr
– Christopher Orr, Commented Sep 21, 2010 at 20:24
Anyone looking for trigger syntax with implementation example in android. coderconsole.com/2015/02/android-sqlite-trigger-demo.html — Nitesh Tiwari
– Nitesh Tiwari, Commented Nov 26, 2016 at 7:13

Shailendra Patil · Accepted Answer · 2014-07-15 09:33:07Z

6

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

edited Jul 15, 2014 at 9:33

Shailendra Patil

3753 silver badges20 bronze badges

answered Sep 21, 2010 at 22:00

Mina Wissa

11k13 gold badges97 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dan · Accepted Answer · 2011-03-26 22:14:28Z

3

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.

answered Mar 26, 2011 at 22:14

Dan

3,1821 gold badge29 silver badges27 bronze badges

Comments

Thomas Mueller · Accepted Answer · 2010-09-21 18:59:43Z

1

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.

answered Sep 21, 2010 at 18:59

Thomas Mueller

50.4k16 gold badges123 silver badges136 bronze badges

2 Comments

Mina Wissa Over a year ago

Thanks Thomas I'll consider using it.

Thomas Mueller Over a year ago

I should probably add: According to my (limited) testing some operations are faster and some are a lot slower. I'm working on that, and you can expect that performance of H2 on Android will improve in the next months. I also want to implement the Android APIs so that switching from SQLite to H2 is easier.

Walery Strauch · Accepted Answer · 2013-09-27 11:26:25Z

1

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;"
);

edited Sep 27, 2013 at 11:26

Walery Strauch

7,1288 gold badges56 silver badges61 bronze badges

answered Sep 27, 2013 at 10:49

Sudhir Patil

312 bronze badges

Comments

mreichelt · Accepted Answer · 2010-09-19 10:48:52Z

0

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

answered Sep 19, 2010 at 10:48

mreichelt

12.5k7 gold badges60 silver badges72 bronze badges

3 Comments

Mina Wissa Over a year ago

thanks for your help, but can you provide me with any reference to this issue

Mina Wissa Over a year ago

I believe your assumption is wrong because it does support triggers

mreichelt Over a year ago

It seems so. I searched for it, and from what I read I think that these features are available, but not activated by default. I don't know how to use triggers yet, but there is an interesting method in the class SQLiteDatabase called markTableSyncable (String table, String foreignKey, String updateTable) which maybe is what you want: developer.android.com/reference/android/database/sqlite/…

Collectives™ on Stack Overflow

SQLite triggers in Android?

5 Answers 5

Comments

Comments

2 Comments

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

2 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related