Hi Myles,

> In order to improve performance on a query, I would like to add a trigger to
> a table to automatically update a 'disply' field with some values. The
> values would look like this:
>
> 'XX,YY,ZZ'
>
> And are based on the number of related resources that are allocated to a
> slot. My table structure looks kinda like this:
>
> <ALLOCATION> 1 --- N <RESOURCES>
>
> (but it’s a bit more complex in real life). Each resource has Initials, and
> this is what I want to show in the XX, YY, ZZ field. In order to do this, I
> believe I will need a small stored procedure that can be called from within
> a trigger, that will select all of the allocated resources, and then loop
> through the rows returned, extracting the Initials and creating a string
> variable, seperated by commas for display.
>
> I can see the syntax for a WHILE loop in PSQL, however how do you associate
> this with the number of rows returned from a SELECT statement? I need to
> loop through each row returned and add to the string variable I'm
> constructing.
>
> All pointers greatly appreciated.

Not bullet-proof, but the following might get you onto the right track.

A selectable SP for getting a list of resource initials for a given
allocation.

SET TERM ^^ ;
CREATE PROCEDURE P_RESOURCELIST (
ALLOCATION_ID Integer)
returns (
RES_LIST VarChar(100))
AS
declare variable vInitial varchar(5);
begin
res_list = '';
for select initial from resources where allocation_id =
:allocation_id into :vInitial do
begin
if (res_list = '') then
begin
res_list = vInitial;
end else
begin
res_list = res_list || ',' || vInitial;
end
end
if (res_list = '') then
begin
res_list = null;
end
suspend;
end
^^
SET TERM ; ^^


Then you need an universal trigger on the table RESOURCES which uses the
SP and updates a field in the ALLOCATION table.

SET TERM ^^ ;
CREATE OR ALTER TRIGGER TRI_RESOURCES FOR RESOURCES ACTIVE AFTER DELETE
OR INSERT OR UPDATE POSITION 0 AS
begin
if (deleting) then
begin
update allocation set resource_conc = (select res_list from
p_resourcelist(old.allocation_id))
where id = old.allocation_id;
end else
begin
update allocation set resource_conc = (select res_list from
p_resourcelist(new.allocation_id))
where id = new.allocation_id;
end
end
^^
SET TERM ; ^^


Welcome to the wonderful world of serverside programming. ;-)

HTH!


--
Best Regards,
Thomas Steinmaurer
LogManager Series - Logging/Auditing Suites supporting
InterBase, Firebird, Advantage Database, MS SQL Server and
NexusDB V2
Upscene Productions
http://www.upscene.com