RollBack Transaction

• Dhukkaieswaran.N

  Newbie

  Points: 1

  More actions

  January 21, 2011 at 9:27 pm

  #230679

  I wand help for how to rollback the transaction.

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  January 22, 2011 at 7:01 am

  #1276892

  Will this example assist you?

  CREATE TABLE [dbo].[TranTest](

  [Col1] [int] NOT NULL,

  [Col2] [int] NULL,

  CONSTRAINT [PK_TranTest] PRIMARY KEY CLUSTERED

  (

  [Col1] ASC

  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

  ) ON [PRIMARY]

  --=========================

  CREATE PROCEDURE Tran_Test

  AS

  BEGIN TRANSACTION

  BEGIN TRY

  --==This statement does NOT generate an error

  INSERT TranTest (Col1) VALUES (10)

  -- This statement generates an error

  -- Since Col1 is defined as NOT NULL

  INSERT TranTest (Col2) VALUES (100)

  END TRY

  BEGIN CATCH

  ROLLBACK

  END CATCH

  IF @@TRANCOUNT > 0

  COMMIT

  EXECUTE Tran_Test

  --==This statement does not return any rows

  --==since both inserts were rolled back and

  --==NOT commited to the database

  SELECT * FROM TranTest

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]