oracle trigger not recognizing insert

Question

I am working on a system using Oracle DB. An ETL tool (ODI) put the following trigger on a table to enable changed data capture (CDC).

create or replace trigger SCHEMA.T$TABLE_NAME
after insert or update or delete on SCHEMA.TABLE_NAME
for each row
declare
    V_FLAG  VARCHAR(1);
    V_ROW_ID    VARCHAR2(60);
begin

    if updating then
        V_ROW_ID := :new.ROW_ID;
        V_FLAG := 'U';
    end if;

    if inserting then
        V_ROW_ID := :new.ROW_ID;
        V_FLAG := 'I';
    end if;

    if deleting then
        V_ROW_ID := :old.ROW_ID;    
        V_FLAG := 'D';
    end if;

    insert into SCHEMA.J$TABLE_NAME 
    (
        JRN_SUBSCRIBER,
        JRN_CONSUMED,
        JRN_FLAG,
        JRN_DATE,
        ROW_ID
    )
    select  JRN_SUBSCRIBER,
        '0', 
        V_FLAG, 
        sysdate,
        V_ROW_ID
    from    SCHEMA.SNP_SUBSCRIBERS
    where   JRN_TNAME =  'SCHEMA.TABLE_NAME'
    /* The following line can be uncommented for symetric replication */
    /* and  upper(USER) <> upper('SCHEMA') */
    ;
end;

My issue is that this thing doesn't recognize updates. For instance, when I do a very simple update on one row, it still inserts an 'I' into the CDC table, signifying that it read the update as an insert. Whats up? This some odd oracle thing? I haven't read about it anywhere.

Thanks in advance!

Answer 1

Try removing the "for each row" and it should work .

i had the same problem as well before

EDIT : sorry i didnt read your trigger well.. it wont work anymore without the EACH ROW

See my related question : http://stackoverflow.com/questions/3118223/how-to-prevent-an-insert-trigger-from-being-fired-when-no-row-is-inserted

Answer 2

Well, I ended up just constraining the trigger to run on inserts only. That worked for my purposes.