If insert fails for one record delete all previously inserted row

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?

Answer 1

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 

Answer 2

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

Answer 3

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!