At 11:53 AM 21-06-02 -0500, Hugh Johnson 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.

You haven't provided enough information. What is the table which owns the
trigger? Guessing that it is SPECNO, an After Update trigger should use
its own NEW.* values to select and update one or more rows in another table.

For example:
ALTER TRIGGER SPECIMENNO_INSERT
AFTER INSERT
POSITION 0
AS
BEGIN
UPDATE PTDEMOG
SET SPECIMENID = new.SPECIMEN_NO
where PATHNO = new.PATHNO
END

This is not necessarily going to update only one row in PTDEMOG, unless
PATHNO is its primary key.

If I've guessed wrongly about the trigger owner, come back with the correct
info.

For future SQL problems, we'd appreciate your taking the question to the
ib-support list, since this forum is about IB Objects...


regards,
Helen Borrie (TeamIBO Support)

** Please don't email your support questions privately **
Ask on the list and everyone benefits
Don't forget the IB Objects online FAQ - link from any page at
www.ibobjects.com