Re: Error Handling Coming Out of a Trigger

Well, I have decided to move the protection against the business rule into
the stored procedure and circumvent the execution of the DML. That will
avoid the trigger all together.

If anyone still has any input, I'm up for hearing it...


Thx,

Brian Hollister

--
Got a big event coming up? Let us
help coordinate your event. For more
visit www.kissemgoodbye.com
"Brian Hollister" <bhollisterATfuturaintlDOTcom> wrote in message
news:46795ad0$1@xxxxxxxxxxxxxxxxxxxxxxxxx
Hi,


I have an after update trigger on a table. This trigger is used to help
enforce a business rule of not allowing a zero quantity.

I have the following code in the body of the trigger. I had expected that
it
would have caused an exception in the calling application; because i am
using the RAISERROR statement. I also tried several different severity
levels but none worked for me. I get nothing back at all, no message and
certainly no exception.

-- Note this check below will allow for a "zero cost item" to be saved to
the

-- database. It will not however let the Quantity of any SL to be

-- zero or less.

If (@Qty IS NULL) OR (@Qty < 1) OR (@UC IS NULL) or (@UC < 0)

or (@Allowed IS NULL) OR (@Allowed < 0)

BEGIN

ROLLBACK TRANSACTION

SET @ErrMsg = 'The Service Line is not sufficiently setup to ' +

'effectively manage the GL system. The Quantity, Fee, and ' +

'Allowed values must be set prior to attempting to save. ' +

'Review these values and try again.'

RAISERROR(@ErrMsg, 16, 1);

RETURN

END



The update command that causes the invoking of the trigger is done in a
seperate thread. I hope this is not hiding the exception from me
somehow????
The thread object is built with an exception handler of its own. Here's
the
code for that precisely where the sql statement is executed.

procedure TFutCmdThread.DoDataAction;
begin
If dbg_ActiveDebugSession then
If DBG_AllThreads then
DebugServer.Write('TFutCmdThread.DoDataAction HIT: FProcedureName
=
' +
FProcedureName + '; FormID = ' + IntToStr(FFormID));

try
TADOStoredProc(FDataset).ExecProc;
If DoesParamExistInProc(FProcedureName, '@ErrMsg') then
If
TADOStoredProc(FDataset).Parameters.ParamByName('@ErrMsg').Value
<> '' then
begin
FErrorMessage :=

SafeVariant(TADOStoredProc(FDataset).Parameters.ParamByName('@ErrMsg').Value
, '');
FErrorMessageSent := true;
end;
except on E: Exception do
begin
If DoesParamExistInProc(FProcedureName, '@ErrMsg') then
If
TADOStoredProc(FDataset).Parameters.ParamByName('@ErrMsg').Value
<> '' then
begin
FErrorMessage :=

SafeVariant(TADOStoredProc(FDataset).Parameters.ParamByName('@ErrMsg').Value
, '');
FErrorMessageSent := true;
end;
If FErrorMessage <> '' then
FErrorMessage := FErrorMessage + #13#10;
Windows.MessageBox(0, PChar(FErrorMessage + E.Message),
PChar('Error'),
MB_TASKMODAL + MB_SETFOREGROUND + MB_TOPMOST + MB_OK +
MB_DEFBUTTON1 + MB_ICONEXCLAMATION);
end;
end;
end;


I'd like to point out that another Thread Object type we created, a
TFutDataThread, has no problem handling exceptions should it have one to
handle and that its messages are displayed as expected.


Also, if i run an update statement through QA i do see my raiserror
message,
just not from my application. The code rollsback the transaction as i want
but there is no notice to the user of the failure.

How can i get the business rule violation that happens in the trigger code
to bubble up to my calling application with a meaningful message?

Thank you,

Brian Hollister
Futura Intl. Inc.


--
Got a big event coming up? Let us
help coordinate your event. For more
visit www.kissemgoodbye.com




.

Quantcast