Muhammad Hakim Muhammad Hakim - 2 days ago 4
SQL Question

SQLite Triggers Error on launching android apps

I'm trying to create an app that using triggers on its DB. Whenever i try to run it on Android Studio the apps is working well and no error in the code. But when I try to run it on MEmu or a Lollipop mobile phone its show me some error message. I've tried to fix it, but now there is this message when i tried to launch it after some fixing.


java.lang.RuntimeException: Unable to start activity ComponentInfo{com.dexpensemanager/com.dexpensemanager.Home}: android.database.sqlite.SQLiteException: qualified table names are not allowed on INSERT, UPDATE, and DELETE statements within triggers (code 1): , while compiling: CREATE TRIGGER if not exists sumcalc_income AFTER INSERT ON TRANS FOR EACH ROW WHEN TRANS.type= income AND strftime ('%Y'-'%m','now','start of month') BEGIN insert into SUMTRANS.income SELECT SUM(income) FROM TRANS.amount END;
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2460)
at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2522)
at android.app.ActivityThread.access$800(ActivityThread.java:169)
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1421)
at android.os.Handler.dispatchMessage(Handler.java:111)
at android.os.Looper.loop(Looper.java:194)
at android.app.ActivityThread.main(ActivityThread.java:5546)
at java.lang.reflect.Method.invoke(Native Method)
at java.lang.reflect.Method.invoke(Method.java:372)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:967)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:762)
Caused by: android.database.sqlite.SQLiteException: qualified table names are not allowed on INSERT, UPDATE, and DELETE statements within triggers (code 1): , while compiling: CREATE TRIGGER if not exists sumcalc_income AFTER INSERT ON TRANS FOR EACH ROW WHEN TRANS.type= income AND strftime ('%Y'-'%m','now','start of month') BEGIN insert into SUMTRANS.income SELECT SUM(income) FROM TRANS.amount END;
at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:898)
at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:509)
at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
at android.database.sqlite.SQLiteProgram.(SQLiteProgram.java:58)
at android.database.sqlite.SQLiteStatement.(SQLiteStatement.java:31)
at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1704)
at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1635)
at com.example.connectionDB.DBnTrigger.onCreate(DBnTrigger.java:45)
at android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:251)
at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:163)
at com.example.connectionDB.DBnTrigger.getLastIncome(DBnTrigger.java:459)
at com.dexpensemanager.Home.onCreate(Home.java:61)
at android.app.Activity.performCreate(Activity.java:5975)
at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1111)
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2413)
... 10 more


and here's the code for my triggers i take it from create trigger refference

public String insincTrigger() {
String insinc = "CREATE TRIGGER if not exists sumcalc_income "
+ " AFTER INSERT "
+ " ON "+TRANSACTION_TABLE
+ " FOR EACH ROW "
+ " WHEN "
+ " TRANS.type= income "
+ " AND strftime ('%Y'-'%m','now','start of month') "
+ " BEGIN "
+ " insert into SUMTRANS.income "
+ " SELECT SUM(income) FROM TRANS.amount "
+ " END; ";

return insinc;
}

public String insexpTrigger(){
String insexp = "CREATE TRIGGER if not exists sumcalc_expense "
+ " AFTER INSERT "
+ " ON "+TRANSACTION_TABLE
+ " FOR EACH ROW "
+ " WHEN "
+ " TRANS.type= expense "
+ " WHERE strftime ('%Y'-'%m','now','start of month') "
+ " BEGIN "
+ " insert into SUMTRANS.expense "
+ " SELECT SUM(expense) FROM TRANS.amount "
+ " END; ";
return insexp;
}

public String upincTrigger(){
String upinc = "CREATE TRIGGER if not exists usumcalc_income "
+ " AFTER UPDATE "
+ " OF [amount] "
+ " ON "+TRANSACTION_TABLE
+ " FOR EACH ROW "
+ " WHEN "
+ " TRANS.type= income "
+ " WHERE strftime ('%Y'-'%m','now','start of month') "
+ " BEGIN "
+ " UPDATE OR REPLACE "+TRANSACTION_SUM
+ " SET [income] = SELECT SUM(income) from TRANS.amount "
+ " END; ";
return upinc;
}

public String upexpTrigger() {
String upexp = "CREATE TRIGGER if not exists usumcalc_expense "
+ " AFTER UPDATE "
+ " OF [amount] "
+ " ON "+TRANSACTION_TABLE
+ " FOR EACH ROW "
+ " WHEN "
+ " TRANS.type= expense "
+ " WHERE strftime ('%Y'-'%m','now','start of month') "
+ " BEGIN "
+ " UPDATE OR REPLACE "+TRANSACTION_SUM
+ " SET [expense] = SELECT SUM(expense) from TRANS.amount "
+ " END; ";
return upexp;
}

public String delTrigger(){
String del = "CREATE TRIGGER if not exists sumcalc_delete "
+ " AFTER DELETE "
+ " ON "+TRANSACTION_TABLE
+ " FOR EACH ROW "
+ " WHEN "
+ " TRANS.type = income AND expense "
+ " WHERE strftime ('%Y'-'%m','now','start of month') "
+ " BEGIN "
+ " DELETE FROM "+TRANSACTION_SUM
+ " WHERE = SELECT SUM(income AND expense) from TRANS.amount "
+ " END; ";
return del;
}

public String instimeTrigger(){
String instime = "CREATE TRIGGER if not exists sumcalc_instime "
+ " AFTER INSERT "
+ " ON "+TRANSACTION_SUM
+ " FOR EACH ROW "
+ " BEGIN "
+ " insert into SUMTRANS.month "
+ " VALUES ('%Y'-'%m','now','start of month') "
+ " END;";
return instime;
}

public String deltimeTrigger(){
String deltime = "CREATE TRIGGER if not exists sumcalc_instime "
+ " AFTER DELETE "
+ " ON "+TRANSACTION_SUM
+ " FOR EACH ROW "
+ " BEGIN "
+ " DELETE FROM SUMTRANS.month "
+ " END; ";
return deltime;
}


could someone tell me what should i do?

THANKS.

Answer

The exception stack is clearly stating the problem:

android.database.sqlite.SQLiteException: qualified table names are not allowed on
INSERT, UPDATE, and DELETE statements within triggers (code 1)

From the very linked SQLite docs in your question (emphasis mine):

Syntax Restrictions On UPDATE, DELETE, and INSERT Statements Within Triggers

The UPDATE, DELETE, and INSERT statements within triggers do not support the full syntax for UPDATE, DELETE, and INSERT statements. The following restrictions apply:

  • The name of the table to be modified in an UPDATE, DELETE, or INSERT statement must be an unqualified table name. In other words, one must use just "tablename" not "database.tablename" when specifying the table. The table to be modified must exist in the same database as the table or view to which the trigger is attached.

  • The "INSERT INTO table DEFAULT VALUES" form of the INSERT statement is not supported.

  • The INDEXED BY and NOT INDEXED clauses are not supported for UPDATE and DELETE statements.

  • The ORDER BY and LIMIT clauses on UPDATE and DELETE statements are not supported. ORDER BY and LIMIT are not normally supported for UPDATE or DELETE in any context but can be enabled for top-level statements using the SQLITE_ENABLE_UPDATE_DELETE_LIMIT compile-time option. However, that compile-time option only applies to top-level UPDATE and DELETE statements, not UPDATE and DELETE statements within triggers.

  • Common table expression are not supported for statements inside of triggers.

I suppose SUMTRANS in statement insert into SUMTRANS.expense is the database name. As per the docs, that's not supported.

Hope this helps.

Comments