Sqlite: create trigger programmatically

Question

When my App gets installed for the first time, I create a database programmatically. Works fine so far. But when I want to add a trigger, I get an exception saying "syntax error near IF". The problem is, that I can not span the String over multiple lines in source code. So what can I do instead?

private void createTriggers(SQLiteDatabase database) {

    final String triggerLocationArchived = "CREATE TRIGGER location_archived AFTER UPDATE ON location FOR EACH ROW BEGIN IF NEW.deleted <> OLD.deleted THEN UPDATE sector set sector.deleted = NEW.deleted WHERE sector.location = NEW._id; END IF; END;";

    database.execSQL(triggerLocationArchived);
}

There are two tables: location and sector. Each has an INTEGER flag indicating if the row has been archived. So I want all children (sectors within a location) to be archived as well if the parent (the location) is archived.

JoeyRA · Accepted Answer · 2014-07-24 15:14:06Z

1

Try this command:

 CREATE TRIGGER location_archived 
 AFTER UPDATE ON location 
 FOR EACH ROW 
 WHEN NEW.deleted <> OLD.deleted 
 BEGIN 
    UPDATE sector 
    set deleted = NEW.deleted 
    WHERE location = NEW._id; 
 END

Your code will look like this:

private void createTriggers(SQLiteDatabase database) {

     final String triggerLocationArchived = "CREATE TRIGGER location_archived AFTER UPDATE ON [location] FOR EACH ROW WHEN NEW.deleted <> OLD.deleted BEGIN UPDATE sector set deleted = NEW.deleted WHERE location = NEW._id; END";

     database.execSQL(triggerLocationArchived);
}

edited Jul 24, 2014 at 15:14

answered Jul 24, 2014 at 10:40

JoeyRA

4784 silver badges5 bronze badges

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

6 Comments

Matthias Over a year ago

You can not define a String in multiple lines within Java source Code

JoeyRA Over a year ago

Formatting is for display only. Your command is wrong. The exception, "syntax error near IF", happened because you can't use "if", look again, I changed the "if" to "when". Remove formatting and put in your code. It'll work.

Matthias Over a year ago

Oh, I missed that part. Tried it now but does not work either. Now it says error "near "WHEN": syntax error"

JoeyRA Over a year ago

It should work. If you change only the "if" to "when" in your code, this won't work . I removed the "begin" before "for each row" and I changed the "then" after "for each row" to "begin" ,too. Copy my command and paste in your code.

Matthias Over a year ago

Did not work :( Says syntax error near "." I'm using this line (added another column to the UPDATE): CREATE TRIGGER location_archived AFTER UPDATE ON location FOR EACH ROW WHEN NEW.deleted <> OLD.deleted BEGIN UPDATE sector set sector.deleted = NEW.deleted, sector.sync = 1 WHERE sector.location = NEW._id; END;END;

|

Collectives™ on Stack Overflow

Sqlite: create trigger programmatically

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related