Listening to triggers

[hajsf]

I've the below code where I registered a trigger successfuly:

package main

import (
	"database/sql"
	"log"

	sqlite "github.com/mattn/go-sqlite3"
)

func validate(y int64) int64 {
	return y % 2
}

func main() {
	sql.Register("sqlite3_custom", &sqlite.SQLiteDriver{
		ConnectHook: func(conn *sqlite.SQLiteConn) error {
			if err := conn.RegisterFunc("validate", validate, false); err != nil {
				return err
			}
			return nil
		},
	})

	db, err := sql.Open("sqlite3_custom", ":memory:")
	if err != nil {
		log.Fatal("Failed to open database:", err)
	}
	defer db.Close()

	_, err = db.Exec("create table foo (department integer, profits integer)")
	if err != nil {
		log.Fatal("Failed to create table:", err)
	}

	// https://stackoverflow.com/q/27214529/864438 sez error-message must be constant
	mkTrigger := `
	CREATE TRIGGER insert_trigger
	BEFORE INSERT ON foo
	WHEN validate(NEW.department) != 0
	BEGIN
	  SELECT RAISE(ABORT, 'bad validate');
	END;
	`
	_, err = db.Exec(mkTrigger)
	if err != nil {
		log.Fatalf("error creating trigger %q: %s", err, mkTrigger)
		return
	}

	_, err = db.Exec("insert into foo values (5, 2)")
	if err != nil {
		log.Println("Failed to insert first:", err)
	}
}

My question is, can I define a function that listen to these triggers whenever a trigger is sent?

[rittneje]

@hajsf I'm not sure what you mean by "a trigger is sent". What specifically are you intending to accomplish?

[hajsf]

What specifically are you intending to accomplish

What I need is to create a func at go, that keep listening to the database triggers, once a triger is recieved I need ny code to notify me about it.

code example:

func listen(db, action){
    notification := recieve_trigger_notification_from_the_database
    println(notification)
}

In the pg for exa,mple, we can have something like this

func waitForNotification(l *pq.Listener) {
	select {
	case <-l.Notify:
		fmt.Println("received notification, new work available")
	case <-time.After(90 * time.Second):
		go l.Ping()
		// Check if there's more work available, just in case it takes
		// a while for the Listener to notice connection loss and
		// reconnect.
		fmt.Println("received no work for 90 seconds, checking for new work")
	}
}

[rittneje]

I think there may be some confusion as to what a trigger is. The purpose of a trigger is to have SQLite (the C library) do some action (either validating the incoming data or making cascading edits to the database) in response to some incoming change. It isn't really intended to notify your client application of arbitrary changes.

Note that triggers are run as part of the same thread and transaction that is performing the database operation, and the setup you have here (invoking a user-defined function from a trigger) will not work if the modification is performed by another connection to the same database file. (That functionality is not supported by SQLite. You would have to implement some inter-process notification mechanism yourself.)

If your intent is to have some Go code asynchronously triggered whenever the database is modified by your connection, one option is to use RegisterUpdateHook. See sqlite3_update_hook for more details. Please note the caveat about WITHOUT ROWID tables.

[rittneje]

@hajsf Can this issue be closed?