在GoSQL中处理数据库触发器

Question

I'm currently learning to use the Go sql drivers (with MySQL) to talk to a database, and I want to insert a trigger that will be activated when a new record is inserted into a table.

I know that to do this in pure SQL, the statement needs to look like:

CREATE TRIGGER ins_sum BEFORE INSERT ON table_1 
FOR EACH ROW SET @sum = @sum + new.amount

but I'm not sure what this returns, so don't know how to structure as a go/sql query.

Thanks!

Answer 1

If creating the trigger was successful you'll get nil as the second return value from db.Exec if, on the other hand, the query failed you'll get an error with details for why.

For example:

_, err := db.Exec(`CREATE TRIGGER ins_sum BEFORE INSERT ON table_1 FOR EACH ROW SET @sum = @sum + new.amount`)
if err != nil {
    fmt.Println(err)
}
// Output: none

Let's say you run the same query a second time

_, err := db.Exec(`CREATE TRIGGER ins_sum BEFORE INSERT ON table_1 FOR EACH ROW SET @sum = @sum + new.amount`)
if err != nil {
    fmt.Println(err)
}
// Output: Error 1359: Trigger already exists

Or let's say you mistype the table name

_, err := db.Exec(`CREATE TRIGGER ins_sum BEFORE INSERT ON table_987 FOR EACH ROW SET @sum = @sum + new.amount`)
if err != nil {
    fmt.Println(err)
}
// Output: Error 1146: Table 'dbname.table_987' doesn't exist