Ad:

MS SQL Discussion Thread
Unsolved
Views: 851

Jul 1st, 2009

Code inside CATCH not execute

Expand Post »

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:

MS SQL Syntax (Toggle Plain Text)
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
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?

Similar Threads

Is it possible to code inside XML file for an RSS Feed? in RSS, Web Services and SOAP
Problems with executeNonQuery and connection in VB.NET
Avoid the Page_Load after clicking dropdownlist in ASP.NET
Button activate other button in C++
Text from txt To Table in MSACCESS in Java

fulvio1981

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

6 posts
since Jul 2009

Permalink

Jul 1st, 2009

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:

MS SQL Syntax (Toggle Plain Text)
BEGIN TRY
	DECLARE @INT AS INT
	SET @INT=CAST(GetDate() AS VARCHAR(2))
END TRY
 
BEGIN CATCH
	PRINT 'ERROR'
END CATCH
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':

MS SQL Syntax (Toggle Plain Text)
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
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

NextCom

Reputation Points: 18

Solved Threads: 0

Light Poster

Offline

26 posts
since Dec 2008

Permalink

Jul 1st, 2009

Re: Code inside CATCH not execute

Click to Expand / Collapse Quote originally posted by NextCom ...

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

Why not?

In MSDN there's this explanation of RAISERROR:

Quote ...

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...

Quote ...

SELECT 1/0

This is an error! but code in CATCH are not execute.

any idea?
thanks for any reply!

bye

fulvio1981

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

6 posts
since Jul 2009

Permalink

Jul 2nd, 2009

Re: Code inside CATCH not execute

This gode writes ERROR:

MS SQL Syntax (Toggle Plain Text)
BEGIN TRY
	SELECT 1/0
END TRY
 
BEGIN CATCH
	PRINT 'ERROR'
END CATCH
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:

MS SQL Syntax (Toggle Plain Text)
(0 row(s) affected)
ERROR
(0 row(s) affected)
ERROR

NextCom

Reputation Points: 18

Solved Threads: 0

Light Poster

Offline

26 posts
since Dec 2008

Permalink

Jul 2nd, 2009

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!

fulvio1981

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

6 posts
since Jul 2009

Permalink

Jul 2nd, 2009

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:

(Toggle Plain Text)

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

To:

(Toggle Plain Text)

ALTER TRIGGER [dbo].[INS_AlarmsEvents] 
ON [dbo].[TEST] 
    FOR INSERT, UPDATE

NextCom

Reputation Points: 18

Solved Threads: 0

Light Poster

Offline

26 posts
since Dec 2008

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: querry problem

Next Thread in MS SQL Forum Timeline: How to combine multiple rows's column into one column

DaniWeb Message

Cancel Changes

User Name
Password