I cannot add a screenshot due to my reputation. Here is the error I received:
Error source: SqlClient.NET data provider Error message: An error occurred while starting the trigger. The batch was aborted, and the user's transaction, if any, was discarded.
Here is my stored procedure.
ALTER PROCEDURE [dbo].[uspLogError]
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
IF ERROR_NUMBER() IS NULL
RETURN;
IF XACT_STATE() = -1
BEGIN
PRINT 'Cannot log error since the current transaction is in an uncommittable state. '
+ 'Rollback the transaction before executing uspLogError in order to successfully log error information.';
RETURN;
END;
DECLARE
@ErrorMessage NVARCHAR(4000),
@ErrorNumber INT,
@ErrorSeverity INT,
@ErrorState INT,
@ErrorLine INT,
@ErrorProcedure NVARCHAR(200);
SELECT
@ErrorNumber = ERROR_NUMBER(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorLine = ERROR_LINE(),
@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');
SELECT @ErrorMessage =
N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' +
'Message: '+ ERROR_MESSAGE();
INSERT [dbo].[SQLErrorLog]
( [UserName] ,
[ErrorTime] ,
[ErrorNumber] ,
[ErrorSeverity] ,
[ErrorState] ,
[ErrorProcedure] ,
[ErrorLine] ,
[ErrorMessage]
)
VALUES ( CONVERT(SYSNAME, CURRENT_USER) ,
GETDATE() ,
@ErrorNumber ,
@ErrorSeverity ,
@ErrorState ,
@ErrorProcedure ,
@ErrorLine ,
@ErrorMessage
);
END TRY
BEGIN CATCH
PRINT 'An error occurred in stored procedure uspLogError: ';
EXECUTE [dbo].[uspPrintError];
RETURN -1;
END CATCH
RAISERROR
(
@ErrorMessage,
@ErrorSeverity,
1,
@ErrorNumber,
@ErrorSeverity,
@ErrorState,
@ErrorProcedure,
@ErrorLine
);
END;