Hi

I have an application using Hibernate and SQL Server. The database is relatively simple and the application works well. Recently I wanted to add an insert trigger to a table. The trigger inserts a duplicate record into another table. Both table have identity columns. The identity column is used as Hibernate's key. The insert operation is a single record. There are some default values declared, but specifying all of them does not seem to change the problem.

With the trigger in place, the insert fails with this exeception:

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The statement must be run before the generated keys are available.

Using Hibernate 3.2.2 and com.microsoft.sqlserver.jdbc.SQLServerDriver.

This page seems like it may be a clue:

http://forums.microsoft.com/MSDN/ShowPo ... 5&SiteID=1

Very frustrating... Any ideas would appreciated.

I am having the same issue as js001. The triggers I am using work fine when I use them with straight JDBC. However when using Hibernate, I get the error:

com.microsoft.sqlserver.jdbc.SQLServerException: The statement must be run before the generated keys are available.

The table on which I am inserting a row has an IDENTITY column and the trigger uses the IDENTITY value that was inserted when it inserts a row in another table.

I am using hibernate from within the Spring IOC container.

A SQL script of the relevant tables and triggers is below as is portions of the spring configuration.

Jay

------------------------------------------------------------
Database script

-- ===========================
-- report period for a campaign
-- ===========================
CREATE TABLE report_period
(
report_period_id BIGINT IDENTITY NOT NULL,
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
campaign_id BIGINT NOT NULL REFERENCES campaign ( campaign_id ),
report_period_name VARCHAR(100) NOT NULL,
CONSTRAINT pk_report_period UNIQUE CLUSTERED ( report_period_id ASC ),
)
GO

-- ===========================
-- Message counts. This table is updated by triggers. It is present becuase otherwise, calculating
-- the message counts for display in the UI would take too long.
-- ===========================
CREATE TABLE message_count
(
message_count_id BIGINT IDENTITY NOT NULL,
campaign_site_id BIGINT NOT NULL REFERENCES campaign_site ( campaign_site_id ) ON DELETE CASCADE,
report_period_id BIGINT NOT NULL REFERENCES report_period ( report_period_id ) ON DELETE CASCADE,
harvested INT NOT NULL DEFAULT 0,
unscored INT NOT NULL DEFAULT 0,
positive INT NOT NULL DEFAULT 0,
negative INT NOT NULL DEFAULT 0,
neutral INT NOT NULL DEFAULT 0,
bulk_positive INT NOT NULL DEFAULT 0,
bulk_negative INT NOT NULL DEFAULT 0,
bulk_neutral INT NOT NULL DEFAULT 0,
board_count INT,
CONSTRAINT pk_message_count UNIQUE CLUSTERED ( message_count_id ASC ),
CONSTRAINT u_message_count UNIQUE NONCLUSTERED ( campaign_site_id ASC, report_period_id ASC )
)
GO

-- ===========================
-- When a record is added to the report_period table, add a record to the message_count table for each site int the
-- campaign
-- ===========================
CREATE TRIGGER tr_report_period_ins ON report_period AFTER INSERT AS
DECLARE @campaign_site_id BIGINT
DECLARE @campaign_id BIGINT
DECLARE @report_period_id BIGINT
SELECT @report_period_id = i.report_period_id, @campaign_id = i.campaign_id FROM inserted i
DECLARE c1 CURSOR LOCAL FOR SELECT cs.campaign_site_id FROM campaign_site cs WHERE cs.campaign_id = @campaign_id
OPEN c1
FETCH NEXT FROM c1 INTO @campaign_site_id
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO message_count ( campaign_site_id, report_period_id, harvested, unscored, positive, negative, neutral,
bulk_positive, bulk_negative, bulk_neutral )
VALUES ( @campaign_site_id, @report_period_id, 0, 0, 0, 0, 0, 0, 0, 0 )
FETCH NEXT FROM c1 INTO @campaign_site_id
END
CLOSE c1
DEALLOCATE c1
GO

----------------------------------------
Spring configuration

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
<property name="url" value="jdbc:sqlserver://localhost:1433;databaseName=test;selectMethod=cursor"/>
<property name="username" value="test"/>
<property name="password" value="test"/>
<property name="maxActive" value="100"/>
<property name="maxIdle" value="30"/>
<property name="maxWait" value="1000"/>
<property name="defaultAutoCommit" value="true"/>
<property name="removeAbandoned" value="true"/>
<property name="removeAbandonedTimeout" value="60"/>
</bean>


<bean id="sessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
<property name="dataSource" ref="dataSource"/>
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">org.hibernate.dialect.SQLServerDialect</prop>
</props>
</property>
<property name="configurationClass"><value>org.hibernate.cfg.AnnotationConfiguration</value></property>
<property name="configLocation"><value>classpath:hibernate.cfg.xml</value></property>
</bean>

I was able to resolve the problem by adding

SET NOCOUNT ON at the beginning of my trigger (and SET NOCOUNT OFF at the end).

I got the suggestion from http://www.theserverside.com/discussion ... d_id=42533.

Jay

By the way, I think I copied the wrong table into the previous post (if that caused any confusion).