the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.

[RaulJofre]

Having a table like the following, with triggers

CREATE TABLE [dbo].[test](
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	[test] [varchar](50) NULL,
 	CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED ( [id] ASC))
GO
CREATE TRIGGER [dbo].[triggert_test] 
	 ON  [dbo].[test]
	 AFTER  INSERT,DELETE,UPDATE
  AS 
  BEGIN
	  SET NOCOUNT ON;
  END

When using the orm with the create statement, in sql server it returns the following error.

mssql: The target table 'test' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.
[71.636ms] [rows:0] INSERT INTO "test" ("test") OUTPUT INSERTED."id" VALUES ('Prueba');

The error is due to the fact that if the table has triggers it must be used in the OUTPUT statement together with INTO

Example: INSERT INTO "test" ("test") OUTPUT INSERTED."id" into @test VALUES ('Test');

Must declare the table variable "@test"

Is it possible to fix this

[raulci]

We've got the same issue and implemented a workaround, but it would be great if this package supports it directly.

Our workaround is the following. Note our Id is an int, if this package does a fix, the type should be either configured or obtained from the schema.

func createNewTarget[T any](env *HandlerEnv, newTarget *T, result *int64) error {
	stmt := env.Db.Session(&gorm.Session{DryRun: true}).Create(newTarget).Statement
	sql := strings.Replace(stmt.SQL.String(), " VALUES (", " INTO @ids VALUES (", 1)
	sql = fmt.Sprintf("DECLARE @ids TABLE (id INT); %s; SELECT id FROM @ids", sql)

	mapVars := make(map[string]interface{})
	for idx, v := range stmt.Vars {
		mapVars[fmt.Sprintf("p%d", idx+1)] = v
	}
	if err := env.Db.Raw(sql, mapVars).Scan(result).Error; err != nil {
		return err
	}
	return nil
}