[MSSQL] Can't insert when there is a trigger on the table

[AntonioBusillo]

Your Question

I'm trying to insert some values using the Create functions but, since there's an indication about the Primary Key, it automatically adds "OUTPUT INSERTED.ID" into the INSERT INTO returning an error since the table has a trigger. [FIRST CASE]

BUT

If I remove the primary key, it returns "6 rows affected" even if it adds only one row. [SECOND CASE]

I'm using gorm v1.22.5 and dialect gorm.io/driver/sqlserver v1.2.1

The document you expected this should be explained

First Case:

mssql: The target table 'TABLENAME' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.

Second Case:

[65.347ms] [rows:6] INSERT INTO "TABLENAME" ([...]) VALUES ([...]);

Expected answer

Why does it show 6 rows affected? Is there a way to have the ID of the inserted value without using the OUTPUT clause?

[RMTTyszka]

Any news about that? Anyone found a solution? We are having the same issue I supposed it should work, since with other ORMs that works pretty fine

I manage to make it work but i'm not proud of myself, i just hope we have a solution soon

[RMTTyszka]

@AntonioBusillo By the way, the 6 rows are not other actions related to the triggers?

[juddbaguio]

@RMTTyszka would you mind sharing your workaround?

[simoneserra93]

I'm facing the same problem. We mostly managed it by using a struct without the primary key defined, this stops gorm adding the OUTPUT clause. Of course this also stops gorm retrieving the inserted ID, so we have to perform a SELECT after the INSERT when we need the ID.

Has anyone better ideas?

[RubensTen]

Hi, I have the same issue :( The workaround that I tested with my team is using a replace function to remove the OUTPUT, we know this is not the best approach but works

    sql := s.db.GetDb().ToSQL(func(tx *gorm.DB) *gorm.DB {
        return tx.Create(data)
    })
   // from the insert sql generated, delete the OUTPUT 
    sql = strings.Replace(sql, ^OUTPUT INSERTED\."(\w+)", "", 1)
    tx := s.db.GetDb().Exec(sql)

[raulci]

See also here. I proposed a workaround. Note that the issue is more related to the sql-server driver and not directly to gorm.