How to handle sqlite triggers which are based on changed table names/columns?

Question

Lets say I have these 2 tables and trigger:

CREATE TABLE first_table (
 `text` TEXT,
 `num`  INTEGER,
 `id`   INTEGER
);

CREATE TABLE second_table (
 `text` TEXT,
 `num`  INTEGER,
 `id`   INTEGER
);

CREATE TRIGGER first_table_trigger AFTER INSERT ON first_table
BEGIN
INSERT INTO second_table VALUES('NEW LINE', new.num, new.id);
END;

Now, lets suppose my android application changed the second_table (added a column or changed its name to new_second_table). Now first_table_trigger failes indefinitely! How will I handle this from the sql database without having to deal with this in the app code.

I mean, can I dynamically guess the second table name or its columns with in the first_table_trigger ?

I need help on solutions only from the sql/triggers, not from the code please :)

Thanks a lot.

How can I perform "DROP Trigger" ? I can't DROP from inside a trigger...And as I said, I can't do sql commands from the java code. — osman dembele
– osman dembele, Commented Sep 5, 2017 at 9:58

CL. · Accepted Answer · 2017-09-05 12:06:44Z

0

SQLite was designed as an embedded database, to be used together with a 'real' programming language.

Therefore, it does not have any built-in mechanism to create dynamic SQL. In particular, table names cannot be changed dynamically but must be written literally in every SQL statement, and all table names mentions in a statement must exist when the statement is compiled (when the trigger is actually used).

edited Sep 5, 2017 at 12:06

answered Sep 5, 2017 at 11:37

CL.

182k18 gold badges241 silver badges282 bronze badges

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

5 Comments

user2399432 Over a year ago

You mean someone cannot store an SQL command to a string and then use execSQL or rawquery accordingly?

CL. Over a year ago

Yes, SQLite itself does not have execSQL or rawQuery statements.

user2399432 Over a year ago

I am confused.. you mean I cannot do a select as shown in the following link? stackoverflow.com/questions/22169752/…

user2399432 Over a year ago

"..without having to deal with this in the app code." just noticed this precious piece of text. Thanks man!

osman dembele Over a year ago

Do you know why the trigger is evaluated even when its conditions (WHEN) are not confirmed ? I mean, the conditions are false but I still get an error because the sqlite compiles a "no such table" error

MikeT · Accepted Answer · 2017-09-06 07:29:32Z

If you have the ability to copy the database to an SQLite management tool such as SQLite Manager, which I believe would require a rooted device, or that the App has an inbuilt means of backing up the database to an accessible storage location and additionally having the ability of restoring the database from backups at that storage location.

Then you could manually amend the database in the SQLite management tool after copying it e.g. DROP and then CREATE the trigger and then copy the database back to the device to either the database's original location if the device is rooted or to the accessible storage location is using an App that has backup and restore capability.

For example assuming using Android Studio's Android Device Monitor and a package mjt.so46027137 with a database named todo.db then the database can be pulled as below :-

In this Screen shot 2 triggers have been added and the first has been selected for deletion:

The changes can then be pushed back e.g. :-

Running the following:-

    SQLiteDatabase db = databaseHelper.getWritableDatabase();
    Cursor csr = db.rawQuery("SELECT * FROM sqlite_master",null);
    while (csr.moveToNext()) {
        Log.d("SQLITEMASTER","Row=" + csr.getPosition());
        String sqlmstr = "";
        for (int i=0; i < csr.getColumnCount(); i++) {
            sqlmstr = sqlmstr + "\n\t" + "Column=" + csr.getColumnName(i) + " Data in Column=" + csr.getString(i);
        }
        Log.d("SQLITEMASTER", sqlmstr);
    }

which outputs data extracted from the sqlite_master table shows the two triggers (I didn't actually delete the trigger) :-

09-06 17:25:56.791 25300-25300/? D/SQLITEMASTER: Row=0
09-06 17:25:56.792 25300-25300/? D/SQLITEMASTER:    Column=type Data in Column=table
                                                    Column=name Data in Column=android_metadata
                                                    Column=tbl_name Data in Column=android_metadata
                                                    Column=rootpage Data in Column=3
                                                    Column=sql Data in Column=CREATE TABLE android_metadata (locale TEXT)
09-06 17:25:56.792 25300-25300/? D/SQLITEMASTER: Row=1
09-06 17:25:56.792 25300-25300/? D/SQLITEMASTER:    Column=type Data in Column=table
                                                    Column=name Data in Column=student
                                                    Column=tbl_name Data in Column=student
                                                    Column=rootpage Data in Column=4
                                                    Column=sql Data in Column=CREATE TABLE student(_id INTEGER PRIMARY KEY AUTOINCREMENT, todo TEXT)
09-06 17:25:56.792 25300-25300/? D/SQLITEMASTER: Row=2
09-06 17:25:56.792 25300-25300/? D/SQLITEMASTER:    Column=type Data in Column=table
                                                    Column=name Data in Column=sqlite_sequence
                                                    Column=tbl_name Data in Column=sqlite_sequence
                                                    Column=rootpage Data in Column=5
                                                    Column=sql Data in Column=CREATE TABLE sqlite_sequence(name,seq)
09-06 17:25:56.792 25300-25300/? D/SQLITEMASTER: Row=3
09-06 17:25:56.792 25300-25300/? D/SQLITEMASTER:    Column=type Data in Column=trigger
                                                    Column=name Data in Column=test001
                                                    Column=tbl_name Data in Column=student
                                                    Column=rootpage Data in Column=0
                                                    Column=sql Data in Column=CREATE TRIGGER "test001" AFTER INSERT ON "student" BEGIN INSERT INTO student VALUES('ghost'); END
09-06 17:25:56.792 25300-25300/? D/SQLITEMASTER: Row=4
09-06 17:25:56.792 25300-25300/? D/SQLITEMASTER:    Column=type Data in Column=trigger
                                                    Column=name Data in Column=test002
                                                    Column=tbl_name Data in Column=student
                                                    Column=rootpage Data in Column=0
                                                    Column=sql Data in Column=CREATE TRIGGER "test002" AFTER INSERT ON "student" BEGIN Insert INTO student VALUES('ghost2'); END

thus confirming that the triggers have been added.

The above example was done with a Genymotion (emulated) device.

Collectives™ on Stack Overflow

How to handle sqlite triggers which are based on changed table names/columns?

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related