I have 2 tables, SPECNO and PTDEMOG.
SPECNO
SPECIMEN_NO INTEGER PK
PATHNO VARCHAR(15)

PTDEMOG
ID INTEGER PK
.
.
.
PATHNO VARCHAR(15)
SPECIMEN_ID INTEGER

When a record is added to PTDEMOG, SPECNO is checked to see if the
PATHNO exists, if not I am using a trigger, PATHNO_TRIGGER, to create
an entry in the SPECNO table.

ALTER TRIGGER PATHNO_TRIGGER
AFTER INSERT
POSITION 0
AS

BEGIN
INSERT INTO SPECNO ( PATHNO )
SELECT PATHNO FROM PTDEMOG P1
WHERE (NOT EXISTS
( SELECT * FROM SPECNO P2 WHERE
P2.PATHNO = P1.PATHNO )) and
(P1.pathno <> '');
END
This seems to work ok.

I am trying to use a trigger to insert the correct reference to
SPECNO.SPECIMEN_NO in the PTDEMOG.SPECIMEN_ID field using an AFTER
INSERT trigger owned by PTDEMOG, SPECIMENNO_INSERT:

ALTER TRIGGER SPECIMENNO_INSERT
AFTER INSERT
POSITION 0
AS

DECLARE VARIABLE counter INTEGER;


BEGIN
SELECT DISTINCT SPECNO.SPECIMEN_NO
FROM SPECNO WHERE SPECNO.PATHNO = PTDEMOG.PATHNO
INTO :counter;

UPDATE PTDEMOG
SET SPECIMENID = :counter;
END

but it updates all PTDEMOG.SPECIMEN_ID fields with the same
SPECNO.SPECIMEN_NO value.

Any suggestions?

The amatuer,
Hugh Johnson