SQL 2005/FM9 INSERT trigger problem

[Dali]

Hi,

How can I use SQL INSERT/INSTEAD-OF INSERT triggers with FM9.

Filemaker 9 accesses SQL Server 2005 database via System DSN. Only views are visible to FM9. I get an ODBC error when trying to create a record in FM when the underlying view has an INSERT or INSTEAD OF INSERT trigger. Looking at ODBC tracing I see that FM is trying to execute:

"INSERT INTO tablename(column) OUTPUT INSERTED.ID VALUES('Joe')" which is illegal and therefore results in error:

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

Apparently FM is not using the INTO clause which is preventing me from using INSERT triggers. Has anyone had any success?

p.s.

I have had no problems with UPDATE/DELETE triggers.

[David Christie]

We're getting the same problem you described. Did you find a way around it?

David

[elo]

I put the error into google and found this MSFT help page, the relevant text seems to be:

Triggers

Columns returned from OUTPUT reflect the data as it is after the INSERT, UPDATE, or DELETE statement has completed but before triggers are executed.

For INSTEAD OF triggers, the returned results are generated as if the INSERT, UPDATE, or DELETE had actually occurred, even if no modifications take place as the result of the trigger operation. If a statement that includes an OUTPUT clause is used inside the body of a trigger, table aliases must be used to reference the trigger inserted and deleted tables to avoid duplicating column references with the INSERTED and DELETED tables associated with OUTPUT.

If the OUTPUT clause is specified without also specifying the INTO keyword, the target of the DML operation cannot have any enabled trigger defined on it for the given DML action. For example, if the OUTPUT clause is defined in an UPDATE statement, the target table cannot have any enabled UPDATE triggers.

If the sp_configure option disallow results from triggers is set, an OUTPUT clause without an INTO clause causes the statement to fail when it is invoked from within a trigger.