Code inside CATCH not execute
 

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? :)

Re: Code inside CATCH not execute
 

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

Re: Code inside CATCH not execute
 

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

By the way, I've try also to generate an error typing...
This is an error! but code in CATCH are not execute. :-/
any idea?
thanks for any reply! :)
bye

Re: Code inside CATCH not execute
 

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

Re: Code inside CATCH not execute
 

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

Re: Code inside CATCH not execute
 

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] 

    AFTER INSERT, UPDATE

To:

ALTER TRIGGER [dbo].[INS_AlarmsEvents] 

ON [dbo].[TEST] 

    FOR INSERT, UPDATE