BEGIN TRANSACTION/COMMIT TRANSACTION in STORED PROCEDURE called from TRIGGER

• Brad Schroeder

  SSC Veteran

  Points: 287

  More actions

  September 1, 2009 at 9:57 am

  #210157

  Trying to resolve a problem we do an update to a table has a trigger, that trigger calls an SP that does a BEGIN TRANSACTION/COMMIT TRANSACTION around some of the code. When the update is called from a prepared statement in J2EE, we see

  com.microsoft.sqlserver.jdbc.SQLServerException: New request is not allowed to start because it should come with valid transaction descriptor.

  at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)

  at com.microsoft.sqlserver.jdbc.IOBuffer.processPackets(Unknown Source)

  at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown Source)

  at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(Unknown Source)

  at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PreparedStatementExecutionRequest.executeStatement(Unknown Source)

  at com.microsoft.sqlserver.jdbc.CancelableRequest.execute(Unknown Source)

  If we comment out the BEGIN/COMMIT code, it works. Also if we do not do the J2EE code as a prepared statement we do not see the error. The prepared statement does provide some performance so we would like to keep and the BEGIN/COMMIT code is needed to maintain a unit of work involving several updates.

  We are hoping to find someone that can help us understand the exact reason for the error.

  I checked TRANSCOUNT and the counts are correct around the block of BEGIN/COMMIT code. So it looks like the transactions are being managed correctly.

  Thanks

• Ken McKelvey

  SSCoach

  Points: 18895

  More actions

  September 1, 2009 at 11:17 am

  #1047689

  Calling a SP from within a trigger is usually a bad idea. It also suggests that you are using a cursor within the trigger - an even worse idea.

  I suspect that the error message you are getting is something to do with the scope of ROLLBACK.

  If you post the code of the trigger, and any SPs it calls, someone may be able to help you.

• Sergiy

  SSC Guru

  Points: 110209

  More actions

  September 1, 2009 at 11:54 pm

  #1047949

  Trigger is a transaction by itself.

  It's a part of the transaction which fired the trigger.

  It will commit/rollback its own transaction automatically depending if there was an error during execution.

  You cannot force commitment because transaction is not over until trigger (all triggers) are finished with no errors.

  _____________
  Code for TallyGenerator

• Brad Schroeder

  SSC Veteran

  Points: 287

  More actions

  September 2, 2009 at 6:38 pm

  #1048503

  Here is the code outline

  Table Update from J2EE using a prepared statement

  Trigger starts, we check for one field being updated in trigger

  IF UPDATE(STUS_CODE) = 'PUBL'

  CALL SP

  End Trigger

  SP When the table gets a Publish status we create a transaction set to send to other systems

  BEGIN TRANSACTION

  INSERT TABLE for Tracking

  INSERT TABLE for Transaction to send

  INSERT TABLE for notification

  If no error

  COMMIT TRANSACTION

  else

  ROLLBACK

  Error is happening on the COMMIT

  Notes,

  If we do this without a prepared statement no errors, we use a prepared statement because we see a performance improvement and we can have 1000 of transactions per minute so performance is an issue.

  This happens on every other transaction processed, not everyone.

  Thanks for the responses everyone