If insert fails for one record delete all previously inserted row RSS

• • Reply
  zhyanadil.it...

  Member

  75 Points

  513 Posts

  If insert fails for one record delete all previously inserted row

  May 10, 2020 09:26 AM|zhyanadil.it@gmail.com|LINK

  i have this table
  tb1

  id name age fk_f
  1 a 23 a-1
  2 b 33 b-3
  3 c 23 b-3

  this field (fk_f) is foreign key i insert these records at the same time by one execute

  1) insert into tb1 (id,name,age,fk_f) values(4,d,44,'ee')
  2) insert into tb1 (id,name,age,fk_f) values(5,e,43,'ee3')
  3) insert into tb1 (id,name,age,fk_f) values(6,f,23,'ee5')
  4) insert into tb1 (id,name,age,fk_f) values(7,g,22,'ee4')

  i want if error occured during third insert then delete the first and second insert also how can i do it?

• • Reply
  mgebhard

  All-Star

  53021 Points

  23607 Posts

  Re: If insert fails for one record delete all previously inserted row

  May 10, 2020 10:15 AM|mgebhard|LINK

  zhyanadil.it@gmail.com

  i want if error occured during third insert then delete the first and second insert also how can i do it?

  Use a transaction and try...catch.

  BEGIN TRANSACTION;  
  
  BEGIN TRY  
  
  	insert into tb1 (id,name,age,fk_f) values(4,d,44,'ee')
  	insert into tb1 (id,name,age,fk_f) values(5,e,43,'ee3')
  	insert into tb1 (id,name,age,fk_f) values(6,f,23,'ee5')
  	insert into tb1 (id,name,age,fk_f) values(7,g,22,'ee4')
  
  END TRY  
  BEGIN CATCH  
      SELECT   
          ERROR_NUMBER() AS ErrorNumber  
          ,ERROR_SEVERITY() AS ErrorSeverity  
          ,ERROR_STATE() AS ErrorState  
          ,ERROR_PROCEDURE() AS ErrorProcedure  
          ,ERROR_LINE() AS ErrorLine  
          ,ERROR_MESSAGE() AS ErrorMessage;  
  
      IF @@TRANCOUNT > 0  
          ROLLBACK TRANSACTION;
  		
  	PRINT 'Transaction failed'  
  END CATCH;  
  
  IF @@TRANCOUNT > 0  
      COMMIT TRANSACTION;  
  	PRINT 'Transaction completed successfully'
  GO 

• • Reply
  zhyanadil.it...

  Member

  75 Points

  513 Posts

  Re: If insert fails for one record delete all previously inserted row

  May 10, 2020 11:52 AM|zhyanadil.it@gmail.com|LINK

  here is my code isn't delete the previous row

  alter proc [dbo].[INSERT_Sales_Taxes_and_Charges_Template_import]
   @Title varchar(50)=null,
   @company varchar(50)=null,
   @is_default bit=null,
   @disabled bit=null,
    @series varchar(50)=null
  
  
  as
  begin 
  
  
  --set @series='SAL-'
  declare  @Series_1 varchar(50),@series2 varchar(50)
  set @Series_1=( select top 1 series from [Sales_Taxes_and_Charges_Template]  order by ID desc)
  IF(@Series_1 is not null)
  begin
  select @Series2=CONCAT(@series,RIGHT(@Series_1, CHARINDEX('-', REVERSE(@Series_1)) - 1)+1 )
  end 
  else 
  begin 
  SELECT @Series2=CONCAT(@series,'1') 
  
  end
  
  begin try
  set nocount on;
  declare @trancount int;
  set @trancount = @@trancount;
  if @trancount = 0
  begin transaction
  else
  save transaction INSERT_STX_Template_;
  
  set @company= NULLIF(@company,'')
  if len(@company)>0
  begin
  set @company =[dbo].[company1](@company);
  if @company is null
  	Throw 50000,'Selected company not found.',1;
  End
  
  
  
  delete from Sales_Taxes_and_Charges_Template where series=@series
  
  INSERT INTO [dbo].[Sales_Taxes_and_Charges_Template]
  ([series], 
  Title, 
  company_ID, 
  is_default, 
  [disabled]
  
  )
  
  values
  (@Series2,
  @Title,
  @company,
  @is_default,
  @disabled
  )
  lbexit:
  if @trancount = 0		
  commit;
  end try
  begin catch
  declare @error int, @message varchar(4000), @xstate int,@ErrorProc sysname, @ErrorLine INT;
  select @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE(),@ErrorProc = ERROR_PROCEDURE(),@ErrorLine = ERROR_LINE();
  if @xstate = -1
  rollback;
  if @xstate = 1 and @trancount = 0
  rollback
  if @xstate = 1 and @trancount > 0
  rollback transaction
  INSERT INTO ErrorLog (ErrorMsg,  ErrorNumber,  ErrorProc,  ErrorLine) VALUES (@message, @error, @ErrorProc, @ErrorLine)
  raiserror ('INSERT_STX_Template_: %d: %s', 16, 1, @error, @message);
  
  delete from Sales_Taxes_and_Charges_Template where series=@series2
  end catch
  End

• • Reply
  mgebhard

  All-Star

  53021 Points

  23607 Posts

  Re: If insert fails for one record delete all previously inserted row

  May 10, 2020 12:20 PM|mgebhard|LINK

  The second code snippets are nothing like the first.  I tried to read the new code but it is poorly written and not formatted.  Plus it seems you've taken no time to read and understand transactions.   I have no motivation to write your code.

  Debug your code!  Take the time to read the docs and learn how transactions work!