--- In [email protected], soonhuat ong <soonhua...@...> wrote:
>
> Hi all,
> I know it's slightly Delphi Off, but im really in crying needs now and i
> believe some ppls in here have the MssQL knowlesdge . Hope can get some
> respond from my question. The following is my SQL Script
>
> DECLARE @nErr INT
> BEGIN TRAN
> -- Perform work A (Insert into myTable)
> INSERT INTO myTable (Table_ID, Desc) VALUES(1,'Description)
> SELECT @nErr = @@ERROR
> IF @nErr <> 0
> BEGIN
> BEGIN TRANSACTION
> -- Perform works B (Insert into LogTable)
> INSERT INTO LOGTABLE (TABLE_ID, ErrorMsg) VALUES(1, 'Insert Fail')
> COMMIT TRANSACTION
> GOTO QUIT
> END
> QUIT:
> IF @@TRANCOUNT > 0
> ROLLBACK TRAN
> ELSE
> COMMIT TRAN
>
Lets make a little change in your code:
DECLARE @nErr INT
BEGIN TRAN MainTrans --Just an named transaction
-- Perform work A (Insert into myTable)
INSERT INTO myTable (Table_ID, Desc) VALUES(1,'Descripti on)
SELECT @nErr = @@ERROR
IF @nErr <> 0
BEGIN
BEGIN TRANSACTION LogTrans --The log transaction
-- Perform works B (Insert into LogTable)
INSERT INTO LOGTABLE (TABLE_ID, ErrorMsg) VALUES(1, 'Insert Fail')
COMMIT TRANSACTION LogTrans
GOTO QUIT
END
QUIT:
IF @@TRANCOUNT > 0
ROLLBACK TRAN MainTrans
ELSE
COMMIT TRAN MainTrans
Now see in the code as the LogTrans is inside the MainTrans and after that you
are making rollback for MainTrans, so all the transactions after your mainTrans
will be roll back. For your clarification here is a simple code snipt. just
copy and paste in your SQL Query analyzer and watch the result:
begin tran main
insert into myTable (Table_ID, Desc) VALUES(1,'Description')
select * from myTable
begin transaction logtran
insert into myTable (Table_ID, Desc) VALUES(2,'Description-2')
commit transaction logtran
select * from myTable
rollback tran main
select * from myTable
You can easily see the result in three different grids with different values.
Now I thnik your code should be like this:
DECLARE @nErr INT
BEGIN TRAN MainTrans
-- Perform work A (Insert into myTable)
INSERT INTO myTable (Table_ID, Desc) VALUES(1,'Descripti on')
SELECT @nErr = @@ERROR
IF @nErr <> 0
BEGIN
ROLLBACK TRAN MainTrans
BEGIN TRANSACTION LogTrans
-- Perform works B (Insert into LogTable)
INSERT INTO LOGTABLE (TABLE_ID, ErrorMsg) VALUES(1, 'Insert Fail')
COMMIT TRANSACTION LogTrans
--GOTO QUIT
END
ELSE
BEGIN
COMMIT TRAN MainTrans
END
Thanks
Iqbal
