Code inside CATCH not execute - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Code inside CATCH not execute

Author

Topic

fulvio1981
Starting Member

2 Posts

Posted - 2009-07-01 : 11:38:03

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:
[CODE]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[/CODE]
I think the code between BEGIN CATCH...END CATCH are not execute, because I find table "Errors" empty.
Anyone can help me?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-01 : 12:05:02

use severity higher than 10.

Fred

No, you're never too old to Yak'n'Roll if you're too young to die.

fulvio1981
Starting Member

2 Posts

Posted - 2009-07-02 : 06:14:14

I've changed severity with 15, but nothing fire!
I've notice, If I make a query with the same code(I've just deleted the first 3 rows "ALTER TRIGGE....") it work fine and I find the new record in table gestioneimpianti.dbo.Errors with the error.
Why with query fire and with trigger not?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-02 : 06:55:14

Try this for debugging issues

ALTER TRIGGER	dbo.INS_AlarmsEvents
ON		dbo.TEST 
AFTER		INSERT,
		UPDATE 
AS 

BEGIN TRY
	RAISERROR (N'This is message %s %d.', 16, 1, N'number', 5)
	print 'This is printed after the raiserror.'
END TRY

BEGIN CATCH
	print 'This is printed before the insert.'
	INSERT	gestioneimpianti.dbo.Errors([ERROR_NUMBER],[ERROR_SEVERITY],[ERROR_STATE],[ERROR_PROCEDURE],[ERROR_LINE],[ERROR_MESSAGE])
	VALUES	(
			ERROR_NUMBER(),
			ERROR_SEVERITY(),
			ERROR_STATE(),
			ERROR_PROCEDURE(),
			ERROR_LINE(),
			ERROR_MESSAGE()
		)				
END CATCH

Microsoft SQL Server MVP

N 56°04'39.26"
E 12°55'05.63"

nathans
Aged Yak Warrior

938 Posts

Posted - 2009-07-03 : 02:24:54

Any severity > 10 will trip to the catch.

Are you firing the trigger after creating it? I dont see a Insert or Update statement in your test script.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources