Subject RE: [firebird-support] Trigger Syntax
Author ibrahim bulut
I can not see any information in pdf document about built-in LIST function

>>Btw, this SP isn't necessary with FB 2.1 anymore, because there is a
>>built-in LIST function which does exactly this. For example:

>>select list(initial, ',') from resources where allocation_id = 1

-----Original Message-----
[] On Behalf Of Thomas Steinmaurer
Sent: Tuesday, April 03, 2007 9:44 AM
Subject: Re: [firebird-support] Trigger Syntax

Hi Myles,

>> In order to improve performance on a query, I would like to add a trigger
>> 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:
>> (but it's a bit more complex in real life). Each resource has Initials,
>> 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
>> 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
>> 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 ^^ ;
> 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 ; ^^

Btw, this SP isn't necessary with FB 2.1 anymore, because there is a
built-in LIST function which does exactly this. For example:

select list(initial, ',') from resources where allocation_id = 1

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


Visit and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at


Yahoo! Groups Links

__________ NOD32 2165 (20070403) Information __________

This message was checked by NOD32 antivirus system.