canonical / recommended way of creating triggers?

[jubishop]

is there a more structured in-Swift way of creating this trigger or in general preventing a change to the feedURL value as per the example code?

      try db.create(table: "podcast") { t in
        t.autoIncrementedPrimaryKey("id")
        t.column("feedURL", .text).unique().notNull().indexed()
        t.column("title", .text).notNull()
      }
      try db.execute(
        sql: """
          CREATE TRIGGER protectFeedURL
          BEFORE UPDATE OF feedURL ON podcast
          WHEN OLD.feedURL != NEW.feedURL
          BEGIN
          SELECT RAISE(ABORT, 'Updating podcast.feedURL is prohibited');
          END;
          """
      )

[jubishop]

it'd be cool if there was a modifier like invariant()

[groue]

Hello @jubishop,

Triggers are created with raw SQL, just like you do 👍

it'd be cool if there was a modifier like invariant()

Even if it existed, I'm not sure people would look for it.

If you happen to need it several times, you can write a function for it:

extension Database {
    func preventModifications(of column: String, in table: String) {
        let trigger= "protect_\(table)_\(column)"
        let errorMessage = "Modifying \(table).\(column) is prohibited"
        try execute(literal: """
            CREATE TRIGGER \(identifier: trigger)
            BEFORE UPDATE OF \(identifier: column) ON \(identifier: table)
            WHEN OLD.\(identifier: column) <> NEW.\(identifier: column)
            BEGIN
            SELECT RAISE(ABORT, \(errorMessage));
            END
            """)
    }
}

// Usage
try db.create(table: "podcast") { ... }
try db.preventModifications(of: "feedURL", in: "podcast")

Note the use of SQL interpolation (\(identifier: ...)) that helps dealing with table and column names that are an SQLite keyword:

try db.preventModifications(of: "values", in: "group") // fine

[jubishop]

thank you!