question

girish_mi avatar image
girish_mi asked

Error Handling in the SQL Trigger 2008 R2

Hi , I need some guidance in setting up error handling process in the table trigger. I have created a trigger on source database table and it load data to target table whenever there are any changes in last update date of source database ( given code below). Problem : sometime I am getting error message ( like unique Index , data length mismatch etc) and my trigger don’t work and rollback the entire transaction. Requirement : If there is an error with the insertion, I would like to move that error-inducing record into an error table . Any guidance much appreciate. thanks! /****** Object: Trigger Defination ******/ USE [MPSAIntegration] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /*Description: Trigger to insert Asset details into Siebel Table*/ ALTER TRIGGER [dbo].[trg_INS_INTO_CX_PRODPROF_STG] ON [MPSAIntegration].[dbo].[CustomerProductLines] AFTER INSERT,UPDATE AS /****** Get the current Timestamp,Max of SR_NUM from Target table and LastRun time from dbo.TIME ******/ DECLARE @currtime DATETIME, @SR_NUM INT, @Last_Run DATETIME SELECT @currtime = (SELECT CURRENT_TIMESTAMP) SELECT @SR_NUM = (select max(Sr_Num) from dbo.SerialNum WHERE Entity='PROD') SELECT @Last_Run = (SELECT LastRun from [MPSAIntegration].[dbo].[TIME] where ENTITY = 'PROD') BEGIN SET NOCOUNT ON; SET XACT_ABORT ON; /***** Update dbo.SerialNum Table *****/ UPDATE dbo.SerialNum SET Sr_Num = @SR_NUM+1 where Entity='PROD'; /***** Insert into [dbo].[CX_PRODPROF_STG] table *****/ INSERT INTO [ntscrmdbdev].[SiebelDB].[dbo].[CX_PRODPROF_STG] ([ROW_ID] ,[CREATED] ,[CREATED_BY] ,[LAST_UPD] ,[LAST_UPD_BY] ,[MODIFICATION_NUM] ,[CONFLICT_ID] ,[LOAD_STATUS] ,[SR_NUM] ,[INFO_CAPTURE_DATE] ,[ADDRESS_NAME] ,[CUSTOMER_CODE] ,[DESCRIPTION] ,[PRODUCT] ,[SERVER_NAME] ,[STATUS] ,[CANCEL_DATE], [SEQUENCE_ID]) SELECT CAST(CUSTOMERCODE AS NVARCHAR(8))+CAST(@SR_NUM AS NVARCHAR(7)) ,Current_Timestamp ,'dbo' ,Current_Timestamp ,Current_User ,0 ,'N' ,'Not Processed' ,CAST(@SR_NUM AS NVARCHAR(15)) ,InfoCaptureDate ,(SELECT CUST.CUSTOMERNAME FROM CUSTOMERS CUST where CUST.CustomerCode = I.CustomerCode) ,CustomerCode ,ProductLine ,ProductLine ,ServerName ,'ACTIVE' ,TerminationDate ,1 FROM INSERTED I /****** Update the LastRun in dbo.TIME ******/ UPDATE [MPSAIntegration].[dbo].[TIME] SET LastRun = @currtime WHERE ENTITY = 'PROD'; END
sql server 2008 r2error-handling
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.