2个事务执行部门信息中是没有部门编号为3的部门,T2中的IF EXISTS(SELECT * FROM 部门信息 WHERE 部门编号='3')就不成立,而T2无法回滚。结果导致上图的错误提示,可是T1 T2 却都成功执行修改数据,如何让T2中的IF EXISTS(SELECT * FROM 部门信息 WHERE 部门编号='3')不成立就不执行T2,只执行T1?
哪位帮解决下
------解决方案--------------------
应可取消事务T2,把update语句放到if判断中即可,
IF EXISTS(SELECT * FROM 部门信息 WHERE 部门编号='3')
begin
update 员工信息 set 所在部门编号='3' where 员工姓名='李明1'
end
------解决方案--------------------
USE AdventureWorks2012;
GO
IF EXISTS (SELECT name FROM sys.objects
WHERE name = N'SaveTranExample')
DROP PROCEDURE SaveTranExample;
GO
CREATE PROCEDURE SaveTranExample
@InputCandidateID INT
AS
-- Detect whether the procedure was called
-- from an active transaction and save
-- that for later use.
-- In the procedure, @TranCounter = 0
-- means there was no active transaction
-- and the procedure started one.
-- @TranCounter > 0 means an active
-- transaction was started before the
-- procedure was called.
DECLARE @TranCounter INT;
SET @TranCounter = @@TRANCOUNT;
IF @TranCounter > 0
-- Procedure called when there is
-- an active transaction.
-- Create a savepoint to be able
-- to roll back only the work done
-- in the procedure if there is an
-- error.
SAVE TRANSACTION ProcedureSave;
ELSE
-- Procedure must start its own
-- transaction.
BEGIN TRANSACTION;
-- Modify database.
BEGIN TRY
DELETE HumanResources.JobCandidate
WHERE JobCandidateID = @InputCandidateID;
-- Get here if no errors; must commit
-- any transaction started in the
-- procedure, but not commit a transaction
-- started before the transaction was called.
IF @TranCounter = 0
-- @TranCounter = 0 means no transaction was
-- started before the procedure was called.
-- The procedure must commit the transaction
-- it started.
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
-- An error occurred; must determine
-- which type of rollback will roll
-- back only the work done in the
-- procedure.
IF @TranCounter = 0
-- Transaction started in procedure.
-- Roll back complete transaction.
ROLLBACK TRANSACTION;
ELSE
-- Transaction started before procedure
-- called, do not roll back modifications