Markus,

At 07:27 PM 22-08-01 +0200, you wrote:

>Hi all,
>
>in a trigger I have a select statement like
>
>select Key, Field from Tbl where ...
> order by Field desc
>
>I want to store the value of "Key" in a variable of
>the trigger. But only for the first record.
>
>I need to get the Key-Value of the record with Max(Field)
>but unfortuantely Field is not unique and I cannot write
>Select Key, Max(Field) from....
>
>I remember that there was an answer sometimes ago
>that suggested to somehow use rdb$database in the select
>statement to only get one single record.
>
>Can anybody help ?

There isn't logically any way you can guarantee a unique value for Key in this query, since you might have more than one Key having the same value for Field.

You could get the Key from an ordered set, through including some code that places the Key you are looking for first in the set, something like

create trigger....
....
declare variable counter integer;
...
counter=0;
for select Key, Field from aTable
where....
order by Something /* Something determines which row comes first */
into :key, :field do
begin
if counter = 0 then
begin
/* use the current value of :key in your update statement */
...
end
counter = :counter + 1;
end

Other than that, I can't think of anything...

If Field in this other table is some kind of serial number that the trigger is going to update, then you will have to think very hard about the multiple user aspects. Your transaction will have to get exclusive access to that row *before* it starts to do anything...

regards,
Helen
All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________