Database Triggers stopped working in CF8

[Guest]

After upgrading to CF8 cfquery's that hit database insert triggers no longer work.

[threejohn]

I'm seeing the same issue.

[Scott_thornton]

Which database engine?

[Angelos_1]

I had something similar (with mssql), the trigger fired but it didn't return results to CF8, the solution was to use 'other' datasource with the AlwaysReportTriggerResults parameter equals to true. Here is a sample

URL: jdbc:macromedia:sqlserver://localhost:1433;databaseName=testdb;AlwaysReportTriggerResults=true
Driver class: macromedia.jdbc.MacromediaDriver

[Guest]

I filed bug #70238 and Adobe has verified the issue. I'm guessing this will be fixed in a hotfix at some point.

Ben

[EC101]

If you have a lot of datasources like we do and don't want to create them as other you can append ;AlwaysReportTriggerResults=true to the url string in your neo-datasource.xml file.

Existing String
jdbc:macromedia:sqlserver://127.0.0.1:1433;databaseName=thedatabase;
SelectMethod=direct;sendStringParametersAsUnicode=false;MaxPooledStatements=1000

New String
jdbc:macromedia:sqlserver://127.0.0.1:1433;databaseName=thedatabase;
SelectMethod=direct;sendStringParametersAsUnicode=false;MaxPooledStatements=1000;
AlwaysReportTriggerResults=true

This has been tested with MSSQL Server 2000
You will need to stop the coldfusion application service edit the .xml file and then restart CF.

Hope this helps.
Thanks to Angelos_1 for getting this going in the right direction.

Eric

[Richard Davies]

I've tried EC101's solution, in addition to the solution listed at http://www.adobe.com/go/kb402302, but my insert trigger still isn't returning any results to cfquery. This worked fine (and still does) with CF7, but doesn't work on our new CF8 server. We're running CF 8.0.1.195765 and MS SQL Server 2005.

The insert trigger is SELECT * FROM inserted. Our CFML query code is listed below. The qresult variable is set correctly, but addContent is undefined after the query executes, but it should contain the record returned by the database trigger.

Does anyone have any ideas why AlwaysReportTriggerResults=true isn't working or how I can get it to work? Thanks in advance for any help!

[-__cfSearching__-]

Did you try the other suggestion above "... use 'other' datasource with the AlwaysReportTriggerResults parameter ...". Also, if you cfdump the qresult variable, what are the contents?

[Richard Davies]

Yes, I've tried creating an "other" type datasource and manually adding the AlwaysReportTriggerResults trigger. Here's the connecting string I used:
jdbc:macromedia:sqlserver://sqldb:1433;databaseName=cms;AlwaysReportTriggerResults=true

And here's the contents cfdump of qresult:
struct
CACHED false
EXECUTIONTIME 0
IDENTITYCOL 208089
RECORDCOUNT 1
SQL INSERT INTO content (content_name, content_type, content_parent_id, owner_contact_id, summary_text, allow_comments) VALUES ('image.jpg', 'B', 0, 29367, '', 0)

Also, just to be clear, I have also restarted the entire server after making the changes.

[-__cfSearching__-]

What happens if you wrap the insert in a set nocount on/off?

[Richard Davies]

Humm... interesting! It works if I wrap it in a "set nocount" statement, or if I add a single set nocount statement, iregardless of whether I'm setting it to ON or OFF. In fact if I set it to on, and then remove the nocount statement it will still work. But if I set it to off it will work, but then if I remove the nocount statement it breaks again.

Also, when it works, it seems to remove the identitycol that's normally returned in the result structure. (Which isn't really an issue because the trigger is returning the same information... but may be a clue to this puzzle.)

Why does set nocount fix it irregardless of whether it's set to ON or OFF? And why does it work with no set nocount statement if the last nocount value was ON, but not if it was OFF? I'm just trying to understand what is going on here. I'd like to be able to fix this issue without having to identify and modify all of the queries that rely on triggers.

BTW, thank you so much for your help!

[-__cfSearching__-]

> or if I add a single set nocount statement, iregardless of
> whether I'm setting it to ON or OFF

Well, I am not certain if that is actually the case or just what appears to be happening. I am not 100% on this, but I think the "set nocount.." change may apply for the life of the connection. So with pooling, the connection may stay alive across requests and would remember the last setting. Which would account for why it works when the last setting was ON. So it is probably better to use both. ie Change and restore the settings within the same cfquery.

I do not use triggers much, but this seems related to the implementation of the new result attribute. I wrote a few entries about the change in behavior with basic INSERT... VALUES statements. Interestingly, the behavior can changes depending on which driver you use (CF8, MS JDBC 1.0, MS JDBC 1.2)

http://cfsearching.blogspot.com/2008/03/cf8-ms-jdbc-12-driver-generated-key.html
http://cfsearching.blogspot.com/2008/03/cf8-ms-jdbc-12-driver-and-for-my-next.html

[Richard Davies]

Your blog posts helped shed some more light on this issue. I decided to use the SQL Profiler and see what was going on in my case. Without any set nocount statments, I found that "select SCOPE_IDENTITY()" was being appended to my insert statements, just as you pointed out in your blog post. When I add set nocount statements to my query however, "select SCOPE_IDENTITY()" does not get appended to the query.

It appears that the CF8 MSSQL driver uses the nocount statements as a flag to disable the "select SCOPE_IDENTITY()" statement. I thought that AlwaysReportTriggerResults=true was supposed to turn that off... so I'm not sure why I have to use set nocount too.

[-__cfSearching__-]

> It appears that the CF8 MSSQL driver uses the nocount statements
> as a flag to disable the "select SCOPE_IDENTITY()" statement.

Hmm ... I wonder if it is even more basic than that? It seems to work when the first statement is any kind of SET ... or when an UPDATE is the first sql statement. Which suggests that perhaps the keyword "insert" triggers the scope_identity() statement.

UPDATE MyTable SET Something = 'Nothing'
INSERT INTO content (...)
VALUES ( .... )

> I thought that AlwaysReportTriggerResults=true was supposed to
> turn that off... so I'm not sure why I have to use set nocount too.

I am curious, where did you read that?

[Richard Davies]

quote:

---

Originally posted by: -==cfSearching==-
Hmm ... I wonder if it is even more basic than that? It seems to work when the first statement is any kind of SET ... or when an UPDATE is the first sql statement. Which suggests that perhaps the keyword "insert" triggers the scope_identity() statement.

---

Interesting thought! But if I just include a single "set nocount on" statement at the end of my query, then the scope_identify() statement does not appear, despite the fact that the first keyword in this instance is "insert". So it still seems to me that it's the set statement that's acting as the toggle switch.

quote:

---

Originally posted by: -==cfSearching==-
I am curious, where did you read that?

---

I thought that the issue described in http://www.adobe.com/go/kb402302 was the same issue I'm experiencing. (Although now that I've gone back and reread it, I'm not 100% sure they're the same issue). So I assumed that AlwaysReportTriggerResults=true was supposed to solve my issue.

[-__cfSearching__-]

Richard Davies wrote:
> But if I just include a single "set nocount on" statement at the
> end of my query, then the scope_identify() statement does not appear,
> despite the fact that the first keyword in this instance is "insert".
> So it still seems to me that it's the set statement that's acting as
> the toggle switch.

It also works with a declare @var at the end, instead of SET. So I am not sure _what_ the trigger is now 😉 Though it obviously revolves around "insert ... values" somehow.

[Richard Davies]

For what it's worth, and in case it will help anyone else out, I finally found out why this wasn't working for me.

I realized that our table actually has two insert triggers: one to return the primary key of the new record, and a second trigger to update a metadata table. It turns out that CF8 is more sensitive to the order that these triggers execute. By default, the "update metadata table" trigger was running first, followed by the "return key" trigger. This was resulting in the CF query variable being undefined.

However, after switching the execution order of the triggers, it now works correctly (as long as I also have the "AlwaysReportTriggerResults=true" appended to the connection string in the DSN connection.)

The following SQL statement can be used to specify the execution order of the triggers:

===========
USE [database_name]

exec sp_settriggerorder @triggername = 'first_trigger_name', @ORDER = 'first', @stmttype = 'insert', @namespace = NULL

exec sp_settriggerorder @triggername = 'second_trigger_name', @ORDER = 'last', @stmttype = 'insert', @namespace = NULL

go
===========

It's interesting to note that it worked fine in CF7 even with the original trigger order. Adobe should investigate this further to see if there's something that can be done so that it doesn't break backwards compatibility or requires customers to make database modifications to get it to work in CF8.