[Feature] Supports Trigger

[luantranminh]

Proposal solution

GORM has a feature called Hooks (application-level triggers). And now, we want to support database triggers. I'll base the implementation on the Hooks implementation to do the same things.

GORM supports 4 databases now, each has its own way to define a trigger. I'll list out the trigger documentation of each database in order of complexity, from the least trigger options to the most trigger options:

• SQLite
• MySQL8
• PostgreSQL
• SQL Server

We can't have a generic solution that covers all options for triggers. Therefore, I decided to limit the scope to just cover a basic form of trigger: WHEN a specific OPERATION happens DO something.

CREATE TRIGGER trigger_name trigger_time trigger_event

 ON table_name

 BEGIN

 ...

 END

1. Setting up phase

This is the definition of a trigger. One model/table can have multiple triggers

// gormschema/trigger.go

// Trigger defines the necessary fields for a trigger 
type Trigger struct {
    Name      string
    When      string
    Operation string
    Body      string
}

Whenever we want to attach triggers to a model, we can embed the Triggers

// models/pet.go
type Pet struct {
    gorm.Model
    Name   string
    User   User
    UserID uint
}

// WithTriggers helps define database triggers
func (p *Pet) WithTriggers() []gormschema.Trigger {
    return []gormschema.Trigger{
        {
            Name:      "trigger1",
            When:      "BEFORE",
            Operation: "INSERT",
            Body:      "DO SOMETHING",
        }, {
            Name:      "trigger2",
            When:      "AFTER",
            Operation: "UPDATE",
            Body:      "DO SOMETHING",
        },
    }
}

2. Migration phase

We're having a custom migrator to create constraints. I'll base on this and expand its functionality to have a create triggers method

// gormschema/gorm.go
// CreateTriggers creates triggers for the given models.
func (m *migrator) CreateTriggers(models []any) error {
    for _, model := range m.ReorderModels(models, true) {
        if !m.containTrigger(model) {
            continue
        }

        for _, trigger := range model.Triggers {
                       // Parse trigger and execute it 
                       err = m.CurrentDatabase().Exec(trigger.ToSQL())
        }
    }
    return nil
}

func (m *migrator) containTrigger(model any) bool {
        // logic here
    return true
}

For each database, we have a corresponding trigger template

// triggertemlate/mysql.tmpl
CREATE TRIGGER {{ .NAME }}
{{ .WHEN }} {{ .ACTION }} ON {{ .TABLE }}
FOR EACH ROW
BEGIN
   {{ .BODY }}
END

// triggertemlate/sqlite.tmpl
CREATE TRIGGER {{ .NAME }} 
   {{ .WHEN }} {{ .ACTION }} ON {{ .TABLE }}
BEGIN
 {{ .BODY }}
END;

Those templates will be parsed by a method of the Trigger struct

// gormschema/trigger.go
func (t *Trigger) ToSQL(dialect string) (string, error) {
    tmpl, err := template.New(dialect).Parse(dialect)
    if err != nil {
        return "", fmt.Errorf("unsupported engine: %s", dialect)
    }

    var buf bytes.Buffer
    if err := tmpl.Execute(&buf, t); err != nil {
        return "", fmt.Errorf("failed to execute trigger template: %w", err)
    }

    return buf.String(), nil
}

[luantranminh]

I have a new thought! Migration stuff should belong in the migration place. That could be the reason why GORM decided to let the Migrator create Views 🤔

db.Migrator().CreateView()

but not Model.CreateViews()

Therefore, here is another proposal bases on #38, implements gormschema.WithTrigger method.

// gormschema/gorm.go
type (
    TriggerOption struct {
        Query func(*gorm.DB) *gorm.DB
        Replace      bool
    }
)

// WithTrigger sets up a trigger with the given name and options.
func WithTrigger(name string, opt TriggerOption) Option {
    return func(l *Loader) {
        l.afterAutoMigrate = append(l.afterAutoMigrate, func(db *gorm.DB) error {
            return db.Exec(Trigger.New(name, opt).ToSQL())
        })
    }
}

[giautm]

• First, if GORM doesn't support define trigger via its model, then we don't need to do that. Just allow the user define raw SQL for external SQL resources then combine them into the result.
• Second, define trigger via custom structure and Go template is not a correct solution. If you want define model, just use the trigger directly from ariga.io/atlas/sql/schema: https://pkg.go.dev/ariga.io/atlas@v0.21.1/sql/schema#Trigger

[giautm]

Actually, the user can use composite_schema datasource from atlas to combine two schemas (one from GORM, one from their) into one big schema; then atlas can handle migration for them.

[luantranminh]

Oh, thanks. I didn't know Atlas had that. I'll update my proposal soon.

[luantranminh]

Hi @giautm, I'm about to work on this one and will stick with the proposal that defines Triggers() for the desired entity. I read the Trigger definition from https://pkg.go.dev/ariga.io/atlas@v0.21.1/sql/schema#Trigger, but I think it's too verbose for atlas-provider-gorm package and will create confusion with unused fields (Table, View, etc.), isn't it? Therefore, I propose a clean but basic form for a Trigger struct that inherits from the Atlas schema trigger:

type Trigger struct {
    Name       string
    ActionTime schema.TriggerTime // BEFORE, AFTER, or INSTEAD OF.
    Event      schema.TriggerEvent    // INSERT, UPDATE, DELETE, etc.
    For        schema.TriggerFor      // FOR EACH ROW or FOR EACH STATEMENT.
    Body       string                 // Trigger body only.
}

Is this a good practice to have a partial struct from an existing struct and make it more maintainable? Or should I go with a new type for all fields and maintain it inside the atlas-provider-gorm package separately?

type TriggerTime string

const (
    TriggerTimeBefore     TriggerTime = "BEFORE"
    TriggerTimeAfter      TriggerTime = "AFTER"
    TriggerTimeInsteadOf  TriggerTime = "INSTEAD OF"
)

type Trigger struct {
    Name       string
    ActionTime TriggerTime // BEFORE, AFTER, or INSTEAD OF.
    Event      TriggerEvent    // INSERT, UPDATE, DELETE, etc.
    For        TriggerFor      // FOR EACH ROW or FOR EACH STATEMENT.
    Body       string                 // Trigger body only.
}