This is for a Firebird 1.5 Super Server DB on Linux.

I have a trigger that fires on insert and update on a table. In order to update a field with values, the trigger needs to call another stored procedure that will return back the values to update in the table. The stored procedure, however, is also used for general purpose calls, and includes a SUSPEND statement at the end of its operation.

My trigger looks like this:

CREATE OR ALTER TRIGGER JOB_AIU3 FOR JOB
ACTIVE AFTER INSERT OR UPDATE POSITION 3
AS
declare variable V_ALLOC VARCHAR(20);
begin
/* GET ALLOCATED TECHNICIANS */
SELECT CAST(R_ALLOC_INITIALS AS VARCHAR(20)) FROM JOB_TECH_ALLOCATIONS_PRC(NEW.JOB_ID) INTO :V_ALLOC;

/* UPDATE JOB WITH ALLOC INITIALS */
NEW.tech_alloc_display = :V_ALLOC;
end

If I trace through the stored procedure (using IBExpert) can see that it is correctly returning back data. However it never completes the update of the record with that data.

I suspect its because the stored procedure ends with a SUSPEND statement, like this:

for
select :V_ALLOC_INITIALS from RDB$DATABASE INTO :R_ALLOC_INITIALS
do
begin
suspend;
end

Does Firebird allow for the calling of stored procedures to return values in a Trigger, or do I have to enclose the entire logic of the SP into the trigger itself in order for this to work?

Myles