使用事务删除记录 [英] To delete record using transaction

问题描述

我想根据条件从表中删除某些记录。我使用以下事务脚本来实现它。

I want to delete certain records from a table based on a condition. I used the following transaction script to achieve it.

BEGIN TRY
    BEGIN TRANSACTION
	
	DELETE from Consultation
	where SchoolID in
	(
	SELECT SchoolID 
	from Consultation 
	where [SchoolID]=7128
	)
	
    COMMIT TRAN 
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN 
END CATCH

当我执行上述声明时，它显示0行受影响。但是当我使用以下脚本时

When I execute the above statement it shows 0 rows affected. But when I used the following script

BEGIN TRY
    BEGIN TRANSACTION
	
	select * from Consultation
	where SchoolID in
	(
	SELECT SchoolID 
	from Consultation 
	where [SchoolID]=7128
	)
	
    COMMIT TRAN 
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN 
END CATCH

它显示了3行记录。



当我尝试手动删除记录时，记录正在删除。但我认为从表中删除记录不是更安全的方法。任何人都可以帮我找出这背后的问题。



我甚至尝试使用脚本来删除其他一些schoolID并且它工作正常但不仅仅是这个schoolID。我无法找到解决此问题的方法。请帮忙。

it shows me 3 rows of records.

The records are deleting when I tried deleting it manually. But I consider it is not the safer method to delete records from table. Can anyone please help me find out what the issue behind this.

I even tried using script to delete for some other schoolID and it is working perfectly but not for this schoolID alone. I am unable to find out the issue fix for this. Please help.

推荐答案

不是你的查询只是

Isn't your query just

DELETE from Consultation WHERE SchoolID=7128

你肯定不需要子查询？如果你想确保第二个表中的记录存在，那么使用连接而不是in子查询。



http://stackoverflow.com/questions/439750/t-sql-selecting-rows-to-delete-via-joins [ ^ ]

请尝试做一件事。



请在catch块上放一些错误说明看看如果发生任何错误。在您的查询中，如果发生任何错误，它将回滚事务并返回0行受影响。

Please try to do one thing.

Please put some error description on catch block to see if there is any error occurred or not. In your query if any error will occur then it will rollback the transaction and returns "0 Rows Affected".

BEGIN CATCH
    IF @@TRANCOUNT > 0
        Print Error_Message()
        ROLLBACK TRAN
END CATCH

如果您有任何疑问或疑问，请与我们联系。





谢谢

Advay Pandya

Please let me know if you have any concern or query.


Thanks
Advay Pandya

使用交易时请考虑以下事项：



如果你正在做SELECT（你的第二个代码块） - 如果查询失败，你期望回滚什么？



同样，如果你想删除一条记录 - 它是什么如果交易失败，你想回滚吗？



TRANSACTIONS，用更简单的术语来说，对INSERT和UPDATE查询有意义 - 更具体地说，如果它们是不止一次操作（直接或间接），你真正需要一个全有或全无的决定。

Consider the following when using transactions:

If you are doing a SELECT (your second code block) - what is it you expect to roll back if the query fails?

Similarly, if you wish to DELETE a record - what is it you wish to roll back if the transaction fails?

TRANSACTIONS, in more simple terms, make sense with respect to INSERT and UPDATE queries - more specifically, if they're is more than one operation going on (directly or indirectly) where you truly need an all-or-nothing decision.

这篇关于使用事务删除记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！