My friend ask me the following transaction problem. Consider the following structure of tables asTable1(int)Table2(int)Table3(int)
Now consider the following problem.
CREATE procedure [dbo].[insert_SP]
DECLARE @errorcode int
BEGIN TRAN t1
INSERT INTO TaBLE1 VALUES(2)
INSERT INTO TaBLE2 VALUES('A')
SELECT @errorcode = @@ERROR
INSERT INTO TaBLE3 VALUES(3)
COMMIT TRAN t1
PRINT 'Unexpected error occured'
ROLLBACK TRAN t1
Since i am trying to insert Character in Integer it would give me the error hence i would expect that the Statement 'Unexpected error occured' should execute. But that is not the case over here. I notice that transactions are rollback , but it should show the error message as 'Unexpected error occured', which is not happening. I would like to know why this error is occuring.