SQL & PL/SQL

Good Morning All,

Got a dilemma I'm trying to figure out and hope someone can provide some guidance.  I have a trigger created and works just fine that where after a record is inserted/updated/deleted into a table named Attachment, it inserts that a change was made to that attachment table in another table called CHANGES. So for example, here is the structure of Attachment and a sample record:

ATTACHMENT

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

CHANGES

Here is the trigger and this works:

create or replace TRIGGER  "ATTACHMENTS_AFTER_INS_UPD_TRG"

AFTER INSERT OR UPDATE

    ON ATTACHMENT

    FOR EACH ROW

DECLARE

  l_dml_type varchar2(10);

BEGIN

IF (inserting) then

  l_dml_type := 'INS';

END IF;

IF (updating) then

  l_dml_type := 'UPD';

END IF;

INSERT INTO CHANGES

   ( ID,

   TABLENAME,

     REPORT#,

     ACTIONNAME,

     dateinserted)

   VALUES

     (change_ID_SEQ.NEXTVAL,

     'ATTACHMENTS',

     :new.report#,

     l_dml_type,

     sysdate);

END;

My issue is, I now have a requirement to insert a column named VERSIONNUMBER into CHANGes table.  This versionnumber is located in another table which is the parent table to attachment.  The name of the parent table is called REPORT and it is related to attachment by report# column.  So 1 report, can have multiple attachments.  So, now whenever a change is done in attachment I have to also get the current versionnumber from the report table (related by report#) into the changes table.  VersionNumber can change per report depending on other actions to the report that I have not listed here.  So here is what I expect to see now if I updated the same attachement/report# 3 times.

Day 1, insert and the versionnumber from report table was 1 at that time.

Day 2, updated same attachement and versionnumber was still 1 in report table

Day 3, udpated same attachement but now versionnumber has changed to 2 in report table so have to record it as 2 in CHANGES table. 

CHANGES

I hope i explained the situation as clear as I can.  I guess my problem is joining 2 tables to achieve what I am tasked to do.  If there is a better way to do this, please let me know.  Thanks in advance for any help.