insert tirgger problems

[YKB]

Hi,

I have an Asset Management System through FileMaker Pro. But I am using a SQL server back end database.

I am trying to write a trigger in SQL Server so that once a new record is inserted that it updates the barcode number according to the assessment centre that this asset belongs to.

The trigger is as follows:

ALTER TRIGGER [dbo].[Create_Barcode_Number]

ON [dbo].[AssetManagement]

AFTER INSERT

AS

BEGIN

declare @var varchar(255)

declare @code varchar(255)

SET NOCOUNT ON;

if (select AssessmentCenterContractor from inserted) = 'Carlton'

select @var = 'CTN'

if (select AssessmentCenterContractor from inserted) = 'Geelong'

select @var = 'GEE'

begin

select @code = (select UniqueAssetCode from inserted)

select @var = @var + @code

update AssetManagement set AssetManagement.Barcode = @var WHERE AssetManagement.UniqueAssetCode = (select UniqueAssetCode from inserted)

end

END

What it is doing, is checking the assessment centre of the newly inserted asset, abbreviating it and then concatenating the abbreviation to the unique asset code (which is automatically generated)

The trigger works fine through SQL Server, i.e. if I go and insert a new record through SQL Server, the trigger is successful.

But when I insert a new record through Filemaker, and press save it gives me the following error: "ODBC Error: [Microsoft][ODBC SQL Server Driver][sql Server] The target table 'FileMaker.dbo.AssetManagement if the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause"

I am not really sure what this statement means or how to fix it.

Help would be very much appreciated

Thanks

YKB

[elo]

Typing your error message into Google gave this MSFT help page:

The relevant text is:

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.

[YKB]

Hi all,

I still haven't received a solution from this problem...if anyone knows anything, can you please post here! Any help would be appreciated!!

Thanks

YKB

[sirglandon]

I am also experiencing this exact same problem. Has anyone found a solution yet?

[sirglandon]

Some relevant info for this post:

I have a simple SQL table named "MyTable" with a column named "username" and an insert trigger.

I ran a trace in SQL Server Profiler while I attempted to add a record for username "Bubba" with FileMaker.

Here is the corresponding entry in the trace results:

declare @p1 int

set @p1=NULL

exec sp_prepexec @p1 output,N'@P1 nvarchar(50)',N'INSERT INTO MyServer.dbo.MyTable(username) OUTPUT INSERTED."date" VALUES(@P1)',N'Bubba'

select @p1

As you can see, FileMaker is indeed using the OUTPUT clause without INTO.

I also have a column referenced here (date) with a calculated default value GetDate(). I'm looking into whether or not this is related to the issue.

[sirglandon]

The date field has nothing to do with it. It is simply referencing the field specified to FileMaker as the unique identifier when the table is added in Define Database.

I have no idea what is going on in this line but I'm trying to figure it out.

[sirglandon]

I have found an article discussing the syntax of the sp_prepexec procedure.

http://jtds.sourceforge.net/apiCursors.html#_sp_prepexec

[sirglandon]

I'm able to run the code in Management Studio without any issues, therefore FileMaker is not to blame here. So...

Looks like a bug in the ODBC driver. I have checked the FileMaker Server's driver version and it matches the SQL Server.

ODBC Driver I'm using:

SQL Server 2000.86.3959.00 SQLSRV32.DLL 2/17/2007

I guess I need to try a different ODBC driver against the SQL Server to see if that fixes the problem.

[sirglandon]

In my previous post I stated that I could successfully run the FileMaker generated code directly in Management Studio - I was wrong. I had disabled the trigger and forgot to enable it prior to my test. So...

When running this code in Management Studio:

declare @p1 int

set @p1=NULL

exec sp_prepexec @p1 output,N'@P1 nvarchar(50)',N'INSERT INTO MyServer.dbo.MyTable(username) OUTPUT INSERTED.pk VALUES(@P1)',N'me'

select @p1

I get this response:

Msg 334, Level 16, State 1, Line 1

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

Msg 8180, Level 16, State 1, Procedure sp_prepexec, Line 1

Statement(s) could not be prepared.

(1 row(s) affected)

Which is indeed the same as what is being reported back to FileMaker via the ODBC driver. The driver is not at fault, it is the code being generated by FileMaker.

I read in another thread that this error does not appear with an UPDATE or DELETE trigger. I imagine what is taking place here is that in an INSERT, FileMaker is generating a new record and is requesting the unique identifier to be sent back so it can update its own index of the SQL table. When it is updating or deleting a record, it already knows the unique identifier value so doesn't need to use OUTPUT in order to retrieve it.

Since I have administrative access to the SQL server, I may be able to redesign the SQL database so my particular FileMaker application only needs to do UPDATE not INSERT. For others who cannot take this approach, I don't see any way around it except for FileMaker to change the code that gets sent to the SQL Server.

[sirglandon]

If you are a glutton for punishment, or if you are as cursed with thoroughness as I am, then you may want to read this excellent article about the use of OUTPUT in order to retrieve the primary key of a newly inserted row.

He does mention the issue related to triggers AND the proper syntax to use in that case (which requires a table variable).

http://www.dbazine.com/sql/sql-articles/cook18

[sirglandon]

I've submitted a bug report to FileMaker.

[YKB]

Yeh, I’ve already reported this problem to them (a month ago) and I still haven't received a reply from them.

I’ll post the answer back here, if they get back to me... if you could do the same that would be great!

Thanks

[YKB]

Hi sirglandon, just wondering if you have any news regarding this insert trigger issue that we both seem to be having.

I still haven't heard anything back from filemaker, and it's getting to the point in my project where I really need the insert trigger to be working

[jsail]

Hi all,

could anybody find a solution to solve problem with filemaker and the triggers in the ms-sql db?

I think without a solution, FileMaker is not able to create records in the ms-sql db. For reading and updating only, FileMaker is not useful enough as a frontend of sql-db's. (would be a pity!)

joe