How to execute a trigger multiple times in SQLite android?

Question

I am developing an android application that has implementation of SQLite database, i want to create a trigger that runs a number of times utill specificcount is reached.

What is required

There are 2 tables TABLE 1 and TABLE 2

After insert on TABLE 1 a trigger should be called 12 times and insert 12 rows in TABLE 2 based on specific WHERE CLAUSE
On 13th time trigger should not insert any new row.

What i tried

onOpen function in DatabaseHelper class

 @Override
    public void onOpen(SQLiteDatabase db) {
        super.onOpen(db);
        if (!db.isReadOnly()) {
            // Enable foreign key constraints
            db.execSQL("PRAGMA foreign_keys=ON;");
            db.execSQL("PRAGMA recursive_triggers = ON;\n");
        }
    }

onCreate function in DatabaseHelper class

  public void onCreate(SQLiteDatabase db) {
        createTables(db);
         db.execSQL("CREATE TRIGGER multi_insertion AFTER INSERT ON TABLE1 \n" +
                    " WHEN (SELECT COUNT (*) from TABLE2 where MYNUMBER=1) < 12\n" +
                    "  BEGIN\n " +
                    "          insert into SCHEDULE (COLUMN1,COLUMN2) values ('001','002');  \n   " +
                    "  END;");
    }

OUTPUT: It inserted only 1 row in TABLE 2

Can somebody please help me out to achieve this. Any help will be appreciated

davidm · Answer 1 · 2020-01-13T07:02:48.443

One possible solution is to use triggers but you have to be careful and I'm not sure if it is a good practice to solve those kind of problems with a recursive trigger.

Your code couldn't work because you called the trigger on TABLE1 once, which performed one INSERT in TABLE2, and that's all.

However, check the code snippet below. I added another trigger on TABLE2 which insert the same row for 11 times.

@Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("PRAGMA recursive_triggers = ON;\n");

        db.execSQL("CREATE TABLE TABLE1 (NAME VARCHAR(20))");

        db.execSQL("CREATE TABLE TABLE2 (COL1 NUMBER, COL2 NUMBER)");

        db.execSQL("CREATE TRIGGER multi_insertion_table1 BEFORE INSERT ON TABLE1 \n" +
                " WHEN (SELECT COUNT (*) from TABLE2 where COL1=1 and COL2=2) < 12\n" +
                "  BEGIN \n" +
                "          insert into TABLE2 values (1, 2);  \n   " +
                "  END;");

        db.execSQL("CREATE TRIGGER multi_insertion_table2 AFTER INSERT ON TABLE2 \n" +
                " WHEN (SELECT COUNT (*) from TABLE2 where COL1=1 and COL2=2) < 12\n" +
                "  BEGIN \n" +
                "          insert into TABLE2 values (1, 2);  \n  " +
                "  END;");
    }

The second trigger must be an AFTER STATEMENT in this case otherwise the DB will throw an exception with a message too many levels of trigger recursion. Check this answer.

MikeT · Accepted Answer · 2020-01-02T19:22:57.820

I believe that your issue is not that your triggers are wrong in theory BUT that you have 3 tables:-

TABLE1,
TABLE2 and
BN_MS_LS_SCHEDULE

and you are getting the row count from TABLE2 but inserting into BN_MS_LS_SCHEDULE.

This answer is based upon 12 inserts into table 1 triggering 12 times, rather the interpreting After insert on TABLE 1 a trigger should be called 12 times and insert 12 rows as meaning that 12 rows should be inserted when the first row in table1 is inserted, if this is the requirement then you could :-
- either use a single INSERT with mutlitple sets of values e.g. INSERT INTO BN_MS_LS_SCHEDULE (column1,column2) VALUES('001','002'),('003','004') ......
- or use 12 INSERT statements/actions within the BEGIN .... END construct (the given example shows such multiple (2) statements/actions)

At a guess you need to do something like :-

public void onCreate(SQLiteDatabase db) {
    createTables(db);
     db.execSQL("CREATE TRIGGER multi_insertion AFTER INSERT ON TABLE1 " +
                " WHEN (SELECT COUNT (*) from TABLE2 where MYNUMBER=1) < 12" +
                " BEGIN " +
                " INSERT into BN_MS_LS_SCHEDULE (COLUMN1,COLUMN2) VALUES ('001','002');" +
                " INSERT into TABLE2 VALUES(?????????); " + //<<<<<<<<< insert a row into table 2 to act as a counter 
                "  END;");
}

Obviously ?????????? should be replaced by an appropriate value or values.
This will then add a row into table 2 and the WHEN clause will act appropriately.
- As you can see you can have multiple ACTIONS within a TRIGGER's BEGIN .... END construct.

Example

Consider this working example based upon what can be gleaned from your code, with an exception that table2 has had an additional column added which is used to record a value from table1 into table1, thus showing how you can use new.column within a TRIGGER.

DROP TABLE IF EXISTS table1;
DROP TABLE IF EXISTS table2;
DROP TABLE IF EXISTS BN_MS_LS_SCHEDULE;
DROP TRIGGER IF EXISTS multi_insertion;

CREATE TABLE IF NOT EXISTS table1 (mydata);
CREATE TABLE IF NOT EXISTS table2 (mynumber,valuefromtable1);
CREATE TABLE IF NOT EXISTS BN_MS_LS_SCHEDULE (COLUMN1,COLUMN2,OTHERCOLUMNS);


CREATE TRIGGER IF NOT EXISTS multi_insertion
    AFTER INSERT ON table1
    WHEN (SELECT COUNT (*) from TABLE2 where MYNUMBER=1) < 12
    BEGIN
        INSERT INTO table2 VALUES(1,new.mydata /* record value that caused insert */); /*<<<<<<<<<< ADDED TO TRIGGER >>>>>>>>>>*/
        INSERT into BN_MS_LS_SCHEDULE (COLUMN1,COLUMN2) VALUES ('001','002');
    END;


/* Add some testing data which will exceed the 12 rows */
INSERT INTO table1 VALUES('a');
INSERT INTO table1 VALUES('b');
INSERT INTO table1 VALUES('c');
INSERT INTO table1 VALUES('d');
INSERT INTO table1 VALUES('e');
INSERT INTO table1 VALUES('f');
INSERT INTO table1 VALUES('g');
INSERT INTO table1 VALUES('h');
INSERT INTO table1 VALUES('i');
INSERT INTO table1 VALUES('j');
INSERT INTO table1 VALUES('k');
INSERT INTO table1 VALUES('l');
INSERT INTO table1 VALUES('m');
INSERT INTO table1 VALUES('n');
INSERT INTO table1 VALUES('o');
INSERT INTO table1 VALUES('p');
INSERT INTO table1 VALUES('q');
INSERT INTO table1 VALUES('r');

/* Results */
SELECT * FROM table1;
SELECT * FROM table2;
SELECT * FROM BN_MS_LS_SCHEDULE;

/* Cleanup Text Environment */
DROP TABLE IF EXISTS table1;
DROP TABLE IF EXISTS table2;
DROP TABLE IF EXISTS BN_MS_LS_SCHEDULE;
DROP TRIGGER IF EXISTS multi_insertion;

Results

Running the above results in :-

Table1 i.e. with all 18 rows that were inserted.

Table2

THe highlighted column shows data retreived from table 1
as can be seen 12 rows only have been added

BN_MS_LS_SCHEDULE

Likewise only 12 rows have been addded

How to execute a trigger multiple times in SQLite android?

2 Answers2

Example

Results