Hy, you guys!
I'm trying to implement in a trigger write by-me error handling with TRY...CATCH, but I'm loosing a lot of time without success. :'(
Here there's a simple code write for test:

ALTER TRIGGER [dbo].[INS_AlarmsEvents] 
ON [dbo].[TEST] 
    AFTER INSERT, UPDATE 
        AS 
			BEGIN TRY
				RAISERROR (N'This is message %s %d.', -- Message text.
						10, -- Severity,
						1, -- State,
			           N'number', -- First argument.
						5); -- Second argument.
			END TRY
			
			BEGIN CATCH
			
				 INSERT INTO gestioneimpianti.dbo.Errors([ERROR_NUMBER],[ERROR_SEVERITY],[ERROR_STATE],[ERROR_PROCEDURE],[ERROR_LINE],[ERROR_MESSAGE])
					VALUES(
						(SELECT	ERROR_NUMBER() AS ErrorNumber),
						(SELECT	ERROR_SEVERITY() AS ErrorSeverity),
						(SELECT	ERROR_STATE() as ErrorState),
						(SELECT	ERROR_PROCEDURE() as ErrorProcedure),
						(SELECT	ERROR_LINE() as ErrorLine),
						(SELECT	ERROR_MESSAGE() as ErrorMessage)
						)				
			END CATCH

I think the code between BEGIN CATCH...END CATCH are not execute, because I find table "Errors" empty.
Anyone can help me? :)

Your code in the TRY don't fire a error into the catch.

If you run this one you will se that it prints out ERROR:

BEGIN TRY
	DECLARE @INT AS INT
	SET @INT=CAST(GetDate() AS VARCHAR(2))
END TRY

BEGIN CATCH
	PRINT 'ERROR'
END CATCH

But this dosn't print 'ERROR':

BEGIN TRY
	RAISERROR (N'This is message %s %d.', -- Message text.
			10, -- Severity,
			1, -- State,
           N'number', -- First argument.
			5); -- Second argument.
END TRY

BEGIN CATCH
	PRINT 'ERROR'			
END CATCH

Your code in the TRY don't fire a error into the catch.

Why not? :confused: In MSDN there's this explanation of RAISERROR:

Generates an error message and initiates error processing for the session. RAISERROR can either reference a user-defined message stored in the sys.messages catalog view or build a message dynamically. The message is returned as a server error message to the calling application or to an associated CATCH block of a TRY…CATCH construct.

By the way, I've try also to generate an error typing...

SELECT 1/0

This is an error! but code in CATCH are not execute. :-/
any idea?
thanks for any reply! :)
bye

This gode writes ERROR:

BEGIN TRY
	SELECT 1/0
END TRY

BEGIN CATCH
	PRINT 'ERROR'
END CATCH

If you click into messages insted of resoults you will see the text:

(0 row(s) affected)
ERROR

The problem is only with trigger! if I made a query everything work fine. The trigger fire, I'm sure!

If you "Include Actual Execution Plan" what does it say when you Insert or Update a record?

You could also try to change:

ALTER TRIGGER [dbo].[INS_AlarmsEvents] 
ON [dbo].[TEST] 
    [B]AFTER[/B] INSERT, UPDATE

To:

ALTER TRIGGER [dbo].[INS_AlarmsEvents] 
ON [dbo].[TEST] 
    [B]FOR[/B] INSERT, UPDATE
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.