Raising custom messages: a procedure and a trigger.
-
I have a stored procedure. The stored procedure made an update on a view.
Let's say that the view is composed by two tables: Header and Detail.
The view's update is made by a trigger instead of the view. SO when I exec the stored procedure in fact the trigger if fired.
I made my customs error messages using sysmessages and some custom error tables where I clasify the errors from sysmessages. The application will consider only these custom errors (let's say 50001 is one custom error).
For example, If I have the error 547 SQL Server will raise the error from the trigger. After that my custom error (50001) will be raised too. But the trigger stop this second raise because of a return I believe.
Is it something to do or I must make the tables updates from the procedure?
-
Your only safe bet to trap all errors sent from SQL Server, is to trap them at the calling client.
You cannot trap all errors in Transact SQL.
The only place all errors are sure to go is to the client.
This also means that your custom errors may not always be the error that is returned. If something happens that prevents your code from raising your custom errors, they will not be returned, only the servers error will. In such a case, it doesn't matter if it happens within a trigger or a procedure.
There's nothing you can do about it currently, it's how things work.
/Kenneth
-
Let's be more precise.
My custom error = 50001
Let's say the server error raised will be 547
The procedure who update a table it works properly, something like that
declare @error int
update x (a table)
set ...
select @error = @error
if @error <> 0
raiserror (50001, 16, 1, @arg1, arg2 etc)
The application (Java) get both errors (547 and 50001) and 3621 and is evaluated only 50001. So it is very good.
The other situation is that of a view with two tables, and here I have problems.
declare @error int
update x (a view)
set ...
in the trigger asociated it happens the folowings
update a
set ...
select @error = @error
if @error <> 0
raiserror (50001, 16, 1, @arg1, arg2 etc)
update b
set ...
select @error = @error
if @error <> 0
raiserror (50001, 16, 1, @arg1, arg2 etc)
In Java is it posible to capture the errors until a return arrive. The problem is that the trigger, when raise the error 547 make a return and my custom raise cannot be captured. Is it posible to avoid the trigger return?
I believe not but I try, who knows?
-
>In Java is it posible to capture the errors until a return >arrive. The problem is that the trigger, when raise the >error 547 make a return and my custom raise cannot be >captured. Is it posible to avoid the trigger return?
This is what I was trying to say - short answer is no.
There is now ceratin way to capture all complete errormessages, or any custom errorhandling code, in Transact SQL or any other language (like java) until the server issues a return. Until the return, languages like java (ie the clientside) has no knowledge of what goes on inside the Transact SQL statement at all.
And that return value always goes to the client, so that's where you will see stuff like 'object id 1234' instead of 'object id %d' and such.
For custom errors to be raised the requirement is that the custom errorhandling code must be executed. For some types of errors this never happens - the server aborts the batch immediately and you will just see the server error in those cases.
/Kenneth
-
In Oracle you can handle errors better but this it, Oracle is more powerful.
I will move the trigger code into the procedures because in this way I can catch all the errors. Thank's.
-
And my car is faster than yours
But seriously - no product wars please.
The point that I'm trying to make is this...
No, you cannot trap all errors in Transact SQL code - be it in a procedure or a trigger.
At this time, the only certain place to trap errors is at the end of the calling client.
A more robust errorhandling within Transact SQL has been on the wishlist for a long time, but for now this is what we have to work with.
/Kenneth
-
I like very much SQL Server but I try to be objective at the same time.
Any way I am sure that the new version will be a great success and willl resolve this problem. Is a pleasure to write code in Transact SQL comparing with PL SQL. Anyway, nobody is perfect, isnt'it.
-
That is so very true.
It always depends and nothing is perfect.
/Kenneth
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply