Grdb.swift: CREATE TRIGGER

[sobri909]

Hi again :)

I'm just about to bite the bullet and move some of my schema relationship management to triggers. But I'm not seeing any way to create triggers as part of database migrations. There's create(table:), create(index:), but no create(trigger:).

Aaaaaand I just realised this is a silly question, because I can just use db.execute("CREATE TRIGGER ...") inside the migration block. Or at least, I assume I can? I'll try it in a min, to see if I really am that stupid.

Uh, I guess while I'm here I might as well ask my next question instead. Although I assumed I'd be able to figure this one out myself from the docs once I got to it. But anyway...

My trigger will be updating a reciprocal relationship. (when an item's TimelineItem.previousItemId is set to another item's itemId, the corresponding item will have its TimelineItem.nextItemId updated to point back to the first item's itemId, to ensure both sides of the next/prev linked list relationship are consistent).

When that happens I'll need to be aware of it from the client code, so that the relevant modified row(s) can be refetched and the relevant object instances updated with the new knowledge. It looks like TransactionObserver will be my friend there.

The question is, will those transaction observers fire for rows that're updated by a trigger? GRDB presumably can only be aware of rows it explicitly updated itself? Or is the observation happening at a lower level, so all will be fine?

Thanks!

[groue]

Hello @sobri909

For triggers, yes, do use raw do.execute("CREATE TRIGGER ...") statements.

About your second question: SQLite does notify of changes performed by foreign key actions ans sql triggers: your transaction observers should work just fine.

Transaction observers perform at the SQL level. They have two prerequisites:

• You must use and reuse a single connection (queue or pool) for the whole duration of the observation.
• you must let GRDB excute the SQL statements (if you use raw sqlite3_prepare and sqlite3_step C functions, you may not get the expected result).

[sobri909]

Sounds great! Thanks :) Looks like this is going to be more straightforward than I thought.