USE SQL_Course;
GO
SET NOCOUNT ON
DECLARE @cust_no INT
DECLARE @err_number CHAR(20)
DECLARE @err_message CHAR(200)
SELECT @cust_no = 1001
BEGIN TRANSACTION;
--@@TRANCOUNT is set to 1
PRINT 'At BEGIN - '  + LTRIM(STR(@@TRANCOUNT)) + ' - ' + STR(@cust_no)

BEGIN TRY
    -- Generate a constraint violation error;
	-- cust_no = 1001 has associated records in the Orders table
    DELETE FROM Customers
    WHERE cust_id = @cust_no
END TRY

BEGIN CATCH
	SELECT @err_number = ERROR_NUMBER()  
	SELECT @err_message = ERROR_MESSAGE()
	PRINT 'At TRY - '  + LTRIM(STR(@@TRANCOUNT));

    IF @@TRANCOUNT > 0
		ROLLBACK TRANSACTION
		BEGIN
			EXECUTE usp_err_log @cust_no,  @err_number, @err_message
		END;
		PRINT 'At ROLLBACK - '  + LTRIM(STR(@@TRANCOUNT));
END CATCH;

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION
	PRINT 'At COMMIT - '  + LTRIM(STR(@@TRANCOUNT));
GO

Use SQL_Course;
GO
SELECT *  
FROM RollbackLog;

--Use SQL_Course;
--GO
--TRUNCATE TABLE RollbackLog

