It seems that the select statement, that should return only one matching row, is returning more than one.
This way, the statement:
SET SPECIMENID = Multiple values
raises the error.
Try "DISTINCT" to get only one (if it's returning multiple rows with the same value), or revise your database design.
You could also retrieve the value you want to set in a complex query and set it to a variable, and then use that variable in the UPDATE statement.
Just some ideas...
Regards
Marco Menardi

--- In IBObjects@y..., Hugh Johnson <hjohnson@p...> wrote:
> Hello,
> I am trying to use a trigger to update one table with a field in another
> table after it is inserted. I try to update the field PTDEMOG.SPECIMENID
> with the value in field
> SPECNO.SPECIMEN_NO where PTDEMOG.PATHNO = SPECNO.PATHNO.
> I have applied the trigger AFTER INSERT but I get a multiple rows in
> singleton select error after the first record have been updated.
> Here is my feeble attempt at this trigger, I may be going about this the
> wrong way, this is my first really big project.
>
> ALTER TRIGGER SPECIMENNO_INSERT
> AFTER INSERT
> POSITION 0
> AS
> BEGIN
> UPDATE PTDEMOG
> SET SPECIMENID = (Select SPECNO.SPECIMEN_NO
> from PTDEMOG, SPECNO
> where PTDEMOG.PATHNO = SPECNO.PATHNO);
> END
>
> Any suggestions would be greatly appreciated.
>
> Thanks,
>
> Hugh Johnson
> Ark Path Assoc.
> hugh@c...
> hjohnson@p...
>
>
> This message and any included attachments or files are from Arkansas
> Pathology Associates,PA and are intended only for the addressee (s). The
> information contained herein may include trade secrets or privileged or
> otherwise confidential information. Unauthorized review, forwarding,
> printing, copying, distributing, or using such information is strictly
> prohibited and may be unlawful. If you received this message in error, or
> have reason to believe you are not authorized to receive it, please promptly
> delete this message and immediately notify the sender by e-mail.