Hi Helen

Thanks a lot for your help. I am now able to create the trigger and
execute the SP successfully.

--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...> wrote:
>
> At 08:20 PM 1/01/2007, you wrote:
> >Hi Helen
> >
> >Thanks for your response. I will explain what I intend to do so that
> >you and other can help me with this. I am not totally a newbie but I
> >am still approaching the Intermediate stage with FB.
> >
> >I have a parent table sPacklingList and a child Table uStock. uStock
> >contains entries from multiple parent tables, one of which is
> >sPackingList, thus, I was not able to use constraints to delete
> >entries in uStock. I was suggested to use triggers. To make the code
> >reusable (from FB as well as VFP's SPT) I moved the delete code
> > DELETE FROM uStock
> > WHERE iPID = :tiID;
> >into a SP which takes one parameter tiID and use it in the delete
command.
> >
> >Now I am trying to call this SP from the After Delete Trigger where I
> >would like to pass the Old.iID to this SP named DelUStock. I don't
> >need a return value, but if explaining to me is not a big deal I would
> >like to return a boolean value which would return a 1 if the delete
> >was successful otherwise a 0.
>
> First, you can't retrieve a return value from a trigger. You can,
> however, get a return value from your SP. It's hard to think of how
> you might use it, though...
>
> Ok, supposing you do have something in mind to use this return value
> for, where you have gone wrong is in using a SELECT statement to
> invoke your SP. The statement you need is EXECUTE PROCEDURE.
>
> Now, assuming you wrote your SP with a return value, e.g.
>
> create procedure DelUStock(tiID integer)
> returns (RetVal smallint)
> as
> begin
> RetVal = 0;
> [ whatever ];
> if (foo = bar) then
> Retval = 1;
> end
>
> Then, in your trigger, you would need to declare a variable to
> receive the return value, e.g.
>
> create trigger....
>
> as
> declare returnvalue smallint;
> begin
> ...
> In the trigger, invoke your SP with
>
> EXECUTE PROCEDURE DelUStock(OLD.iID)
> returning_values(:returnvalue);
>
> ./heLen
>