sync_schema does not sync triggers

[HansH]

If I change or remove a trigger in the database schema, it is not updated.

Example:

#include <sqlite_orm/sqlite_orm.h>
#include <string>

using namespace sqlite_orm;

struct Lead {
    int id = 0;
    std::string name;
    std::string email;
};

int main() {
    auto storage = make_storage("db.sqlite",
        make_trigger("validate_email_before_insert_leads",
             before()
                 .insert()
                 .on<Lead>()
                 .begin(select(case_<int>()
                     .when(not like(new_(&Lead::name), "%_@__%.__%"), //Oops, wrong column
                        then(raise_abort("Invalid email address")))
                     .end()))
                 .end()),

        make_table("leads",
           make_column("id", &Lead::id, primary_key()),
           make_column("name", &Lead::name),
           make_column("email", &Lead::email)));
    storage.sync_schema();

    return 0;
}

If I run this, and then run sqlite3 db.sqlite .dump, I see this trigger:

CREATE TRIGGER "validate_email_before_insert_leads" BEFORE INSERT ON "leads" BEGIN SELECT CASE WHEN NOT NEW."name" LIKE '%_@__%.__%' THEN RAISE(ABORT, 'Invalid email address') END; END;

If I then change the line

.when(not like(new_(&Lead::name), "%_@__%.__%")

into

.when(not like(new_(&Lead::email), "%_@__%.__%")

the trigger remains unchanged.

Also, if I remove the trigger entirely, it is not removed from the database.

I would at least expect changed triggers to be updated in my database. Maybe it can be argued that triggers shouldn't be removed, but for my use case that doesn't make much sense. If I no longer have it in my schema, I no longer want it.

[HansH]

I see here that it simply does CREATE TRIGGER IF NOT EXISTS .....

I think it would be better to do DROP TRIGGER IF EXISTS ..., followed by CREATE TRIGGER. This solves the most important issue, that triggers aren't updated if you change them in the schema.

[fnc12]

hi @HansH . Thanks for pointing out. Yeah triggers syncing is not perfect cause there is no strict way to get trigger data (trigger reflection) except raw SQL query. So the question is open. If you need to update your trigger just remove it manually using storage.drop_trigger and them run sync_schema again with some schema version flag. Or let's create a working way of syncing triggers and implement it - the project welcomes new contributors as always