Golang框架中数据库监听和触发器的应用

import (
    "context"
    "fmt"
    "time"

    "cloud.google.com/go/pubsub"
)

func listenDatabaseEvents(ctx context.Context, projectID, subscriptionID string) error {
    // Creates a Pub/Sub client
    client, err := pubsub.NewClient(ctx, projectID)
    if err != nil {
        return fmt.Errorf("pubsub.NewClient: %v", err)
    }

    // Subscribe to a Pub/Sub topic that receives Cloud SQL database events
    // You must have a Cloud SQL instance with Pub/Sub integration enabled
    sub := client.Subscription(subscriptionID)

    // Listen for database events
    // The context is canceled when the program is terminated
    for {
        ctx, cancel := context.WithTimeout(ctx, time.Second*10)
        msg, err := sub.Receive(ctx)
        if err != nil {
            cancel()
            return fmt.Errorf("sub.Receive: %v", err)
        }

        fmt.Printf("Received event for database: %s\n", msg.Attributes["database"])

        // Acknowledge the message to delete it from the subscription
        msg.Ack()
        cancel()
    }
}

import (
    "context"
    "fmt"

    "cloud.google.com/go/spanner"
)

func createUserTrigger(ctx context.Context, db string) error {
    stmt := spanner.Statement{
        SQL: `
        CREATE TRIGGER UpdateUserLog
        AFTER INSERT, UPDATE ON Users
        FOR EACH ROW
        WHEN (OLD.Active IS NULL OR NEW.Active IS NULL OR OLD.Active <> NEW.Active)
            INSERT INTO UsersLog (UserId, Message, Timestamp)
            VALUES (NEW.UserId, CONCAT(
                CASE
                    WHEN OLD.Active IS NULL
                    THEN 'User created: '
                    WHEN NEW.Active IS NULL
                    THEN 'User deleted: '
                    ELSE 'User updated: '
                END,
                NEW.Name
            ), CURRENT_TIMESTAMP())
        `,
    }

    _, err := db.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error {
        _, err := txn.Update(ctx, stmt)
        return err
    })

    if err != nil {
        return fmt.Errorf("couldn't create trigger: %v", err)
    }

    return nil
}