locked
If insert fails for one record delete all previously inserted row RRS feed

  • Question

  • User-1634604574 posted

    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?

    Sunday, May 10, 2020 9:26 AM

All replies

  • User475983607 posted

    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 

    Sunday, May 10, 2020 10:15 AM
  • User-1634604574 posted

    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

    Sunday, May 10, 2020 11:52 AM
  • User475983607 posted

    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!

    Sunday, May 10, 2020 12:20 PM