--Asagidaki ornekte try catch bulunmaktadir. asagidaki transaction icinde iki tane sp calisacaktir.up_test ve up_testB. up_test hata verdirmek uzere olusturulmustur. Asagidaki transaction calisirken bir sp hata verirken diger sp dogru calisacak sekilde ayarladni. Maksadimiz transaction icindeki bir sp calisirken hata verirse hepsini rollback edecek mi?Test edildi ve rollback ettigi goruldu. --ONCE LOGLARIN TABLOYA ATILMASI ICIN TABLO OLUSTURULUR USE [CHECKDB] GO /****** Object: Table [dbo].[DBATryCatchErrorLog] Script Date: 05/29/2013 18:46:53 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[DBATryCatchErrorLog]( [DBAtblTryCatchID] [int] IDENTITY(1,1) NOT NULL, [ErrorNumber] [char](10) NULL, [ErrorSeverity] [nchar](10) NULL, [ErrorState] [nchar](10) NULL, [ErrorProcedure] [varchar](100) NULL, [SPName] [varchar](100) NULL, [ErrorLine] [nchar](10) NULL, [ErrorMessage] [varchar](max) NULL, [LogDate] [datetime] NULL, CONSTRAINT [PK_tblTryCatchErrorLog] PRIMARY KEY CLUSTERED ( [tblTryCatchID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[DBATryCatchErrorLog] ADD CONSTRAINT [DF_tblTryCatchErrorLog_LogDate] DEFAULT (getdate()) FOR [LogDate] GO ------------------------------------------ ------------------------------------------ --TEST ICIN ACILAN TABLO USE [CHECKDB] GO /****** Object: Table [dbo].[TryCatchTest] Script Date: 05/29/2013 18:46:23 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[TryCatchTest]( [TryCatchID] [int] IDENTITY(1,1) NOT NULL, [Ad] [nchar](10) NULL, [Soyad] [nchar](10) NULL, [Aciklama] [nchar](10) NULL, CONSTRAINT [PK_TryCatchTest] PRIMARY KEY CLUSTERED ( [DBATryCatchID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO --------------- --------------- --EN DISTAKI ANA TEST PROC. BURADA IKI SP EXECUTE ETTIRILIYOR. SAYET BIRI HATA VERIRSE DIGERLERIDE ROLLBACK YAPARAK DONECEK MI DIYE BAKIYOR. USE [CHECKDB] GO /****** Object: StoredProcedure [dbo].[up_TryCathTest1] Script Date: 05/29/2013 18:48:06 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --ANA TRANSACTION SP; BU SP DE OLABILIR CREATE PROCEDURE [dbo].[up_TryCathTest1] AS BEGIN BEGIN TRANSACTION; BEGIN TRY -- Generate a constraint violation error. INSERT INTO CHECKDB.dbo.TryCatchTest VALUES ('Cuneyd', 'Tanriverdi', 'TryCatchTest') exec up_TryCathTest2 exec up_TryCathTest3 COMMIT TRANSACTION; END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; INSERT INTO ,DBATryCatchErrorLog SELECT ERROR_NUMBER() AS ErrorNumber ,ERROR_SEVERITY() AS ErrorSeverity ,ERROR_STATE() AS ErrorState ,ERROR_PROCEDURE() AS ErrorProcedure ,'up_TryCathTest1' AS 'SPName' ,ERROR_LINE() AS ErrorLine ,ERROR_MESSAGE() AS ErrorMessage ,GETDATE(); END CATCH; END GO ------------------------------- ------------------------------- -- HATALI SP up_TryCathTest2 USE [CHECKDB] GO /****** Object: StoredProcedure [dbo].[up_TryCathTest2] Script Date: 05/29/2013 18:54:05 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER proc [dbo].[up_TryCathTest2] as begin BEGIN TRANSACTION th; BEGIN TRY select 1/0;-- HATA VERDIRIR COMMIT TRANSACTION th; END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; INSERT INTO CHECKDB.dbo.DBATryCatchSubProcErrorLog SELECT ERROR_NUMBER() AS ErrorNumber ,ERROR_SEVERITY() AS ErrorSeverity ,ERROR_STATE() AS ErrorState ,ERROR_PROCEDURE() AS ErrorProcedure ,'TEST2 HATALI' AS 'SPName' ,ERROR_LINE() AS ErrorLine ,ERROR_MESSAGE() AS ErrorMessage ,GETDATE(); END CATCH ; end --------------------------------- --------------------------------- --HATASIZ ICTEKI DIGER SP up_TryCathTest3 USE [CHECKDB] GO /****** Object: StoredProcedure [dbo].[up_TryCathTest3] Script Date: 05/29/2013 18:56:23 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE proc [dbo].[up_TryCathTest3] as begin BEGIN TRANSACTION th; BEGIN TRY select 1; COMMIT TRANSACTION th; END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; INSERT INTO CHECKDB.dbo.DBATryCatchSubProcErrorLog SELECT ERROR_NUMBER() AS ErrorNumber ,ERROR_SEVERITY() AS ErrorSeverity ,ERROR_STATE() AS ErrorState ,ERROR_PROCEDURE() AS ErrorProcedure ,'up_TryCathTest3 HATASIZ' AS 'SPName' ,ERROR_LINE() AS ErrorLine ,ERROR_MESSAGE() AS ErrorMessage ,GETDATE(); END CATCH ; end GO
