> I create a trigger on table A so that whenever A is updated, some
> information from A ( and other tables ) will be created on B.
>
> For some reason, one update on A sometimes creates two or three
> records on B. The first time is when A is updated, and the 2nd ( and
> 3rd ) is 2 or 3 minutes after that.
>
>
>
> The trigger is as follows :
> set term !! ;
> create trigger currinsp_updcon_update for currinsp
> after update as
> declare variable projectnumber char(12);
> declare variable insid char(3);
> BEGIN
> if( EXTRACT( HOUR from NEW.CIS_DATE ) <> 0 OR EXTRACT(
> MINUTE from NEW.CIS_DATE ) <> 0 ) THEN
> BEGIN
> select wks_projkey,ins_id from wksheets JOIN
> inspectors on wks_insfk=ins_pk where wks_pk = OLD.CIS_WKSFK
> into :projectnumber,:insid;
> insert into projectupdate ( upd_proj, upd_inspcode,
> upd_status, upd_datetime, upd_insid, upd_debugcode, upd_nowtime,
> upd_fk ) values ( :projectnumber, OLD.CIS_CODE, NEW.CIS_STATUS,
> NEW.CIS_DATE, :insid, 1, CURRENT_TIMESTAMP, OLD.CIS_PK );
> END
> END !!
> set term ; !!
>
>
> ( The field upd_nowtime is for debugging. It shows the repeated
> records are created 2 or 3 minutes after the first one is created )
>
> Any advice is greately appreciated.
>
> Thanks,
>
> Vu

I think your debugging method has shown you that your trigger is being fired
more than once. That means that you are updating one or more records each
time you do what you do.
take a look at your code and see what you are doing to issue update
statements more than once or updating more than on record.
when you say 2-3 minutes later, then it's either you are updating the same
record 2-3 minutes later or you are committing this additional update(s) 2-3
minutes later and seeing the update from another transaction 2-3 minutes
later
Alan