SQL & PL/SQL

Hi All,

Here is what I am experiencing and would like some suggestions/direction.

I have 2 tables: REPORT and ROAD

Whenever a user creates a new report, a record is inserted into the REPORT table.  In this report table, there is a column named ROADID.  On creation of this report, a user can chose an existing roadid (road that already exists in the ROAD table) or create a NEW road if that road is not existing at all in the ROAD table.  So essentially roadid is the Priamary Key in ROAD table and foreign key in REPORT table.  If roadid already exists in ROAD table, that roadid is inserted into REPORT table, but if ROAD is brand new road, the new road is first populated in ROAD table and then the roadid is inserted into the REPORT table.

I need to capture inserts/updates into the ROAD table in a table called CHANGES.  I do so by having a trigger on ROAD table. 

If an Insert was performed in ROAD, here is what is inserted in the CHANGES table based on trigger:

CHANGES

I also need to know what REPORT# is attached to this new road in the CHANGES table.  The Report# comes from the REPORT table.  So here is the trigger where I am trying to insert into CHANGES table:

create or replace TRIGGER  "ROAD_AFTER_INS_UPD_TRG"

AFTER INSERT OR UPDATE

    ON ROAD

    FOR EACH ROW

DECLARE

  l_dml_type varchar2(10);

  l_report acrs.report.report#%type;

BEGIN

IF (inserting) then

  l_dml_type := 'INS';

END IF;

IF (updating) then

  l_dml_type := 'UPD';

END IF;

select report# into l_report from acrs.report where roadid = :new.roadid;

INSERT INTO CHANGES

   ( ID,

   TABLENAME,

     REPORT#,

     ACTIONNAME,

     ROADID,

     dateinserted)

   VALUES

     (change_ID_SEQ.NEXTVAL,

     'ROAD',

     :new.report#,

     l_dml_type,

    :new.roadid,

     sysdate);

END;

My issue is, when this trigger fires, I receive the following error:

"ORA-01403: no data found

ORA-06512: at "ROAD_AFTER_INS_UPD_TRG"

ORA-04088: error during execution of trigger 'ROAD_AFTER_INS_UPD_TRG'"

Based on the select join on roadid, I am assuming I am getting this error because on a new road, the roadid does not exist just yet in the REPORT table.  Is there any way to work around this, to still get what I need in the changes table?  If I didnt need the report# in the changes table, it would not be an issue, but I need that and the only way to get that data is joining report and road by roadid. 

Please if anyone has any suggestions, I'll gladly take it.  Thanks in advance.