orbitaloop/WebSqlSync — Issue #1: trigger for insert, update with auto-incremented ID field
Author: moneypenny
Created: 2012-02-28 22:45:02+00:00
State: open

I have a model with an integer ID field that I do not populate manually. I was getting entries in the `new_elem` table with `my_table` and the ID `-1`. That is, new rows weren't being marked as needing to be synced because the ID wasn't stored correctly. To fix this, I just made the triggers explicitly AFTER triggers, so that the generated ID would be there. In `initSync`:

```javascript
self._executeSql('CREATE TRIGGER IF NOT EXISTS update_' + curr.tableName + ' AFTER UPDATE ON ' + curr.tableName + ' ' +
'BEGIN INSERT INTO new_elem (table_name, id) VALUES ("' + curr.tableName + '", new.' + curr.idName + '); END;', [], transaction);

self._executeSql('CREATE TRIGGER IF NOT EXISTS insert_' + curr.tableName + ' AFTER INSERT ON ' + curr.tableName + ' ' +
'BEGIN INSERT INTO new_elem (table_name, id) VALUES ("' + curr.tableName + '", new.' + curr.idName + '); END;', [], transaction);
```

References:
http://sqlite.org/lang_createtrigger.html