MS SQL RSS MS SQL RSS

Code inside CATCH not execute

Reply

•
•
Join Date: Jul 2009
Posts: 6
Reputation: fulvio1981 is an unknown quantity at this point 
Solved Threads: 0
fulvio1981 fulvio1981 is offline Offline
Newbie Poster

Code inside CATCH not execute

 
0
  #1
Jul 1st, 2009
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) 
  1. ALTER TRIGGER [dbo].[INS_AlarmsEvents] 
  2. ON [dbo].[TEST] 
  3.     AFTER INSERT, UPDATE 
  4.         AS 
  5. 			BEGIN TRY
  6. 				RAISERROR (N'This is message %s %d.', -- Message text.
  7. 						10, -- Severity,
  8. 						1, -- State,
  9. 			           N'number', -- First argument.
  10. 						5); -- Second argument.
  11. 			END TRY
  12.  
  13. 			BEGIN CATCH
  14.  
  15. 				 INSERT INTO gestioneimpianti.dbo.Errors([ERROR_NUMBER],[ERROR_SEVERITY],[ERROR_STATE],[ERROR_PROCEDURE],[ERROR_LINE],[ERROR_MESSAGE])
  16. 					VALUES(
  17. 						(SELECT	ERROR_NUMBER() AS ErrorNumber),
  18. 						(SELECT	ERROR_SEVERITY() AS ErrorSeverity),
  19. 						(SELECT	ERROR_STATE() AS ErrorState),
  20. 						(SELECT	ERROR_PROCEDURE() AS ErrorProcedure),
  21. 						(SELECT	ERROR_LINE() AS ErrorLine),
  22. 						(SELECT	ERROR_MESSAGE() AS ErrorMessage)
  23. 						)				
  24. 			END CATCH
I think the code between BEGIN CATCH...END CATCH are not execute, because I find table "Errors" empty.
Anyone can help me?
Reply With Quote Quick reply to this message  
•
•
Join Date: Dec 2008
Posts: 21
Reputation: NextCom is an unknown quantity at this point 
Solved Threads: 0
NextCom NextCom is offline Offline
Newbie Poster

Re: Code inside CATCH not execute

 
0
  #2
Jul 1st, 2009
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) 
  1. BEGIN TRY
  2. 	DECLARE @INT AS INT
  3. 	SET @INT=CAST(GetDate() AS VARCHAR(2))
  4. END TRY
  5.  
  6. BEGIN CATCH
  7. 	PRINT 'ERROR'
  8. END CATCH

But this dosn't print 'ERROR':
MS SQL Syntax (Toggle Plain Text) 
  1. BEGIN TRY
  2. 	RAISERROR (N'This is message %s %d.', -- Message text.
  3. 			10, -- Severity,
  4. 			1, -- State,
  5.            N'number', -- First argument.
  6. 			5); -- Second argument.
  7. END TRY
  8.  
  9. BEGIN CATCH
  10. 	PRINT 'ERROR'			
  11. END CATCH
Reply With Quote Quick reply to this message  
•
•
Join Date: Jul 2009
Posts: 6
Reputation: fulvio1981 is an unknown quantity at this point 
Solved Threads: 0
fulvio1981 fulvio1981 is offline Offline
Newbie Poster

Re: Code inside CATCH not execute

 
0
  #3
Jul 1st, 2009
•
•
•
•
Originally Posted by NextCom View Post
Your code in the TRY don't fire a error into the catch.
Why not? 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
Reply With Quote Quick reply to this message  
•
•
Join Date: Dec 2008
Posts: 21
Reputation: NextCom is an unknown quantity at this point 
Solved Threads: 0
NextCom NextCom is offline Offline
Newbie Poster

Re: Code inside CATCH not execute

 
0
  #4
Jul 2nd, 2009
This gode writes ERROR:

MS SQL Syntax (Toggle Plain Text) 
  1. BEGIN TRY
  2. 	SELECT 1/0
  3. END TRY
  4.  
  5. BEGIN CATCH
  6. 	PRINT 'ERROR'
  7. END CATCH

If you click into messages insted of resoults you will see the text:
MS SQL Syntax (Toggle Plain Text) 
  1. (0 row(s) affected)
  2. ERROR
Reply With Quote Quick reply to this message  
•
•
Join Date: Jul 2009
Posts: 6
Reputation: fulvio1981 is an unknown quantity at this point 
Solved Threads: 0
fulvio1981 fulvio1981 is offline Offline
Newbie Poster

Re: Code inside CATCH not execute

 
0
  #5
Jul 2nd, 2009
The problem is only with trigger! if I made a query everything work fine. The trigger fire, I'm sure!
Reply With Quote Quick reply to this message  
•
•
Join Date: Dec 2008
Posts: 21
Reputation: NextCom is an unknown quantity at this point 
Solved Threads: 0
NextCom NextCom is offline Offline
Newbie Poster

Re: Code inside CATCH not execute

 
0
  #6
Jul 2nd, 2009
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
Reply With Quote Quick reply to this message  
Reply

Quick Reply to Thread
This thread is more than three months old.
Perhaps start a new thread instead?
•
•
•
•
•
•
•
•
Message:
•
•
•
•



Similar Threads
Other Threads in the MS SQL Forum
Thread Tools Search this Thread



connectingtodatabaseinuse count cursor database datepart deadlock delete_trigger highperformancecomputing hpc hpcserver2008 ibm iis loop microsoft ms multithreading news permission query server sql sqlserver sqlserver2005 supercomputing uniqueid update view
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC