Error when adding record if Trigger exists on SQL DB

lm0@logic1.com.au

With editor, if I add a record to a table that has a trigger, I get the following error -
"The target table 'TableName' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause."
When I remove the trigger, it works.

I believe the SQL inset statement needs an INTO clause.

samiam

Wondering if you or anyone else has a solution for this? I'm running into the same issue. Thanks!

lm0@logic1.com.au

Unfortunately I have not managed to resolve this.

allan

Hi,

Could you let me know what database type you are using please?

Allan

lm0@logic1.com.au

Sorry, should have mentioned - Sql server

allan

Thanks for the clarification. This turns out to be fairly horrible to fix (in terms of how easy the code is to understand!). If the column could be said to be an identity column then it is as simple as appending:

SELECT SCOPE_IDENTITY() AS insert_id

But I can't say that, and don't want to restrict Editor in that way. So the fix needs to be based on this post from Microsoft.

I've committed the fix and it will be included in Editor 1.5.1, but if you want to make the change immediately, in the DataTables project find DataBaseUtil/Sqlserver/Query.cs and the _Prepare method in it.

Inside that method you will find an if (_type == "insert") block of code. Replace that with the following and then recompile:

            // On insert we need to get the table's primary key value in
            // an 'output' statement, so it can be used
            if (_type == "insert")
            {
                var pkeyCmd = provider.CreateCommand();

                // We need to find out what the primary key column name and type is
                pkeyCmd.CommandText = @"
                    SELECT
                        INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME as column_name,
                        INFORMATION_SCHEMA.COLUMNS.DATA_TYPE as data_type
                    FROM
                        INFORMATION_SCHEMA.KEY_COLUMN_USAGE
                    JOIN
                        INFORMATION_SCHEMA.COLUMNS ON
                            INFORMATION_SCHEMA.COLUMNS.table_name = INFORMATION_SCHEMA.KEY_COLUMN_USAGE.table_name
                            AND INFORMATION_SCHEMA.COLUMNS.column_name = INFORMATION_SCHEMA.KEY_COLUMN_USAGE.column_name
                    WHERE
                        OBJECTPROPERTY(OBJECT_ID(constraint_name), 'IsPrimaryKey') = 1 
                        AND INFORMATION_SCHEMA.KEY_COLUMN_USAGE.table_name = @table
                ";
                pkeyCmd.Connection = _db.Conn();
                pkeyCmd.Transaction = _db.DbTransaction;

                param = pkeyCmd.CreateParameter();
                param.ParameterName = "@table";
                param.Value = _table[0];
                pkeyCmd.Parameters.Add(param);

                using (var dr = pkeyCmd.ExecuteReader())
                {
                    // If the table doesn't have a primary key field, we can't get
                    // the inserted pkey!
                    if (dr.HasRows && dr.Read())
                    {
                        // Insert into a temporary table so we can select from it.
                        // This is required for tables which have a trigger on insert
                        // See thread 29556. We can't just use 'SELECT SCOPE_IDENTITY()'
                        // since the primary key might not be an identify column
                        sql = "DECLARE @T TABLE ( insert_id " + dr["data_type"] + " ); " + sql;
                        sql = sql.Replace(" VALUES (",
                            " OUTPUT INSERTED." + dr["column_name"] + " as insert_id INTO @T VALUES (");
                        sql += "; SELECT insert_id FROM @T";
                    }
                }
            }

Yikes! Just to get the primary key value from an inserted row!

If you try it, let me know how you get on with it.

Regards,
Allan

allan

Urgh - the forum is auto linking the @ - <a href="/forums/profile/T">@ T</a> needs to be replaced with @ T without the spaces!

Allan

samiam

Nice work Allan, that did the trick. Thank you very much!