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
Basically what i want is after 'Perform work A' action, my script will check if
there's any error during record insertion. If there's any error, then the
script will LOG the error msg in LOGTABLE (works B). Otherwise, the script will
just insert the record into myTable without any logging .
My problem now is that the script above will not insert the record into
LOGTABLE even if work A is fails .
It seems like the ROLLBACK TRAN at the end of the statement will rollback the
ALL transaction even though I have the COMMIT TRANSACTION statement right after
the INSERTIOn of the record in LOGTABLE (works B).
ANYONE ??
[Non-text portions of this message have been removed]
