Does SQLite have any specific ordering requirements for how triggers are created? I have a case where an 'UPDATE BEFORE' trigger will not work as expected if an 'UPDATE AFTER' trigger on the same table (but not the same column) is created after it is.
Shouldn't do. What are the trigger definitions etc.?
// Create trigger to ensure that timestamp record is correctly set on record update if(sqlite3_exec(db, "CREATE TRIGGER Table_Updated_update " "AFTER UPDATE ON Table FOR EACH ROW " "BEGIN " "UPDATE Table SET Table_Updated = DATETIME('NOW') " "WHERE rowid = new.rowid; " "END;", NULL, &total_records, NULL) != SQLITE_OK) { return (sqlite3_errcode(db)); }
// Create trigger to ensure that 'owner' is unique
if (sqlite3_exec(db, "CREATE TRIGGER Table_owner_update "
"BEFORE UPDATE ON Table FOR EACH ROW "
"BEGIN "
"SELECT CASE WHEN ( (new.Table_Type_Index IS NOT NULL) "
"AND "
"(SELECT Table_Type_Name = 'owner' FROM Table_Type "
WHERE Table_Type_Index = new.Table_Type_Index) "
"AND ("
"(SELECT Table_Index FROM Table "
WHERE Table_Type_Index = new.Table_Type_Index)"
") IS NOT NULL) "
"THEN RAISE (ABORT, 'Already have an owner')"
"END;"
"END;", NULL, &total_records, NULL) != SQLITE_OK) {
return (sqlite3_errcode(db)); }// Table defs
if (sqlite3_exec(db, "CREATE TABLE Table_Type"
"(Table_Type_Index INTEGER PRIMARY KEY,"
"Table_Type_Name TEXT NOT NULL UNIQUE,"
"Table_Type_Properties BLOB)",
NULL, &total_records, NULL) != SQLITE_OK) { return (sqlite3_errcode(db)); }
if (sqlite3_exec(db, "CREATE TABLE Table"
"(Table_Index INTEGER PRIMARY KEY,"
"Table_ID TEXT NOT NULL,"
"Table_Type_Index INTEGER NOT NULL REFERENCES Table_Type(Table_Type_Index),"
"Table_Created TEXT DEFAULT NULL,"
"Table_Updated TEXT DEFAULT NULL,"
"Table_Group_Index INTEGER NOT NULL REFERENCES Group(Group_Index),"
"Table_Access_Group_Index INTEGER NOT NULL REFERENCES Group(Group_Index))",
NULL, &total_records, NULL) != SQLITE_OK) { return (sqlite3_errcode(db)); }
========================================================================== "A cat spends her life conflicted between a deep, passionate and profound desire for fish and an equally deep, passionate and profound desire to avoid getting wet. This is the defining metaphor of my life right now."
