应可取消事务T2,把update语句放到if判断中即可,IF EXISTS(SELECT * FROM 部门信息 WHERE 部门编号='3') begin update 员工信息 set 所在部门编号='3' where 员工姓名='李明1' end
用begin trans不管开了多少了事务,rollback一次就全部回滚了,begin trans --trans count: 1 begin trans -- trans count: 2 rollback trans --trans count: 0 commit trans --报错
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 -- made before the procedure was called. IF XACT_STATE() <> -1 -- If the transaction is still valid, just -- roll back to the savepoint set at the -- start of the stored procedure. ROLLBACK TRANSACTION ProcedureSave; -- If the transaction is uncommitable, a -- rollback to the savepoint is not allowed -- because the savepoint rollback writes to -- the log. Just return to the caller, which -- should roll back the outer transaction. -- After the appropriate rollback, echo error -- information to the caller. DECLARE @ErrorMessage NVARCHAR(4000); DECLARE @ErrorSeverity INT; DECLARE @ErrorState INT; SELECT @ErrorMessage = ERROR_MESSAGE(); SELECT @ErrorSeverity = ERROR_SEVERITY(); SELECT @ErrorState = ERROR_STATE(); RAISERROR (@ErrorMessage, -- Message text. @ErrorSeverity, -- Severity. @ErrorState -- State. ); END CATCH GO
看样子应该是这个问题,可是本人新手,不知道如何实际运用到自己的情况里,麻烦能帮编辑下嘛?下面给下你我的电子版: BEGIN TRANSACTION T1 UPDATE 员工信息 SET 员工姓名='李明' WHERE 员工编号='1' IF EXISTS(SELECT * FROM 员工信息 WHERE 员工姓名='李明') BEGIN BEGIN TRANSACTION T2 UPDATE 员工信息 SET 所在部门编号='3' WHERE 员工姓名='李明' IF EXISTS(SELECT * FROM 部门信息 WHERE 部门编号='3') COMMIT TRANSACTION T2 ELSE BEGIN ROLLBACK TRANSACTION T2 END COMMIT TRANSACTION T1 END ELSE ROLLBACK TRANSACTION T1
说明一下: 当IF EXISTS(SELECT * FROM 部门信息 WHERE 部门编号='3')成立时,执行这个update语句(相对于原来的提交事务). 不成立时,则不执行这个update语句(相对于原来的回滚事务).
说明一下: 当IF EXISTS(SELECT * FROM 部门信息 WHERE 部门编号='3')成立时,执行这个update语句(相对于原来的提交事务). 不成立时,则不执行这个update语句(相对于原来的回滚事务). 因为我是想做一个嵌套事务的练习,所以想知道如果是在嵌套事务的情况下该如何处理
begin
update 员工信息 set 所在部门编号='3' where 员工姓名='李明1'
end
begin trans -- trans count: 2
rollback trans --trans count: 0
commit trans --报错
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
-- made before the procedure was called.
IF XACT_STATE() <> -1
-- If the transaction is still valid, just
-- roll back to the savepoint set at the
-- start of the stored procedure.
ROLLBACK TRANSACTION ProcedureSave;
-- If the transaction is uncommitable, a
-- rollback to the savepoint is not allowed
-- because the savepoint rollback writes to
-- the log. Just return to the caller, which
-- should roll back the outer transaction. -- After the appropriate rollback, echo error
-- information to the caller.
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT; SELECT @ErrorMessage = ERROR_MESSAGE();
SELECT @ErrorSeverity = ERROR_SEVERITY();
SELECT @ErrorState = ERROR_STATE(); RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
END CATCH
GO
BEGIN TRANSACTION T1
UPDATE 员工信息 SET 员工姓名='李明' WHERE 员工编号='1'
IF EXISTS(SELECT * FROM 员工信息 WHERE 员工姓名='李明')
BEGIN
BEGIN TRANSACTION T2
UPDATE 员工信息 SET 所在部门编号='3' WHERE 员工姓名='李明'
IF EXISTS(SELECT * FROM 部门信息 WHERE 部门编号='3')
COMMIT TRANSACTION T2
ELSE
BEGIN
ROLLBACK TRANSACTION T2
END
COMMIT TRANSACTION T1
END
ELSE
ROLLBACK TRANSACTION T1
请删除begin transaction T2和commit transaction T2.
不成立时,则不执行这个update语句(相对于原来的回滚事务).
不成立时,则不执行这个update语句(相对于原来的回滚事务).
因为我是想做一个嵌套事务的练习,所以想知道如果是在嵌套事务的情况下该如何处理