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

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?

Comment From: 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

Comment From: RMTTyszka

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

Comment From: juddbaguio

@RMTTyszka would you mind sharing your workaround?

Comment From: 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?

Comment From: 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)

Comment From: 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.