Trigger for inserting only new records in an existing table

Hi Slightwv,

Please provide code for implementing your suggestion 1. Would it be a trigger, or a procedure?? I would prefer a trigger. Thanks.

I'm thinking an after insert trigger on B01.

As far as providing the code:  There are a TON of examples.  There is nothing special about the trigger.

just
select count(*) into some_variable from a01 where col1=:new.col1 and col2=:new.col2 ... and rownum=1;

then:
if some_variable = 0 then
    insert into a01(col1,col2,col3,col4,col5) values(:new.col1,:new.col2,:new.col3,:new.col4,:new.col5);
end if;

You may hit the "mutating table" error if you try to do this directly in an "after insert for each row" trigger, so you may need a combination of:
1. A procedure to accept five input parameters, do the query and the insert if needed
2. An "after insert for each row" to call the procedure and pass it the five "new.col..." values.

Try that combination.  If that still hits the "mutating table" error, you will have to add:
1. a place to hold the five values for each row from your "after insert for each row" trigger (I prefer to use a global temporary table, but a PL\SQL array could also be used).
2. an "after statement" trigger with no "for each row" clause to query the rows from the global temporary table, and call the procedure for each row.

>>You may hit the "mutating table" error

I don't see how.  The trigger is on B01 and the select is against A01.  What are you seeing?

The select is looking for the duplicate in A01 and possibly inserting into A01.

The trigger is on B01.  I don't see how it would mutate.

Hi Slightwv/markgeer,

Thanks.

In my environment, we are unlikely to experience "mutating tables" issue.  Records in Table B01 are INSTERTED, not UPDATED.

>>we are unlikely to experience "mutating tables" issue

Updates have nothing to do with that error unless there is possibly an update trigger..

That error happens when you access(select) from the table that is firing the trigger.

"That error happens when you access(select) from the table that is firing the trigger."

Correct

I may be mistaken here.  This syntax may be OK in a "for each row" trigger on table B01:

"select count(*) into some_variable from a01 where col1=:new.col1 ..."

>>This syntax may be OK in a "for each row" trigger on table B01

Yes, it should be OK.  That is what was confusing me by your post about mutating triggers.

you truncate B01 ...
why make it so difficult ?

insert into B01
select distinct * from A01

>>why make it so difficult ?

I don't think B01 is everything in A01.  I think B01 is a staging table that is loaded by some process.  then all 'new' records from B01 need to be migrated over to A01 once the 'batch' has been processed.

create or replace trigger insert_a01
after insert on b01
declare
v_sql varchar2(255);
begin
v_sql := 'merge into a01 a using b01 b on (a.col1 = b.col1) ==> this assumes col1 is a key
when not matched then
insert values(b.col1, b.col2, b.col3, b.col4, b.col5)';
execute immediate v_sql;
end;
/

-Pretty sure MERGE is considered DML so no need for the execute immediate.

-Would be interesting to see if merge will outperform the select where rownum=1.

>> However, with a lot of rows a statement trigger will usually outperform a trigger for each row

I think I remember reading something like this a long time ago.  Maybe the merge with a statement trigger over a select/insert row by row  would be better.

Hi Experts,

Please be assured, I am constantly viewing the comments. I am in the process of implementing the "merge" solution. Will update soon. The discussions among other experts are very enriching as well.

Thanks.

Hi Experts,

Instead of using a trigger, we have decide to use a Merge with Insert Into, When Not Matched clause.

I have the statement below. I am getting  error    "PL/SQL: ORA-02012: missing USING keyword". But I have the USING key word.

Merge into A01 (FINDG_KEY, FINDG_TYP, CONCEPT, TRIGGER_TXT, SRCH_TERM_ID) A
                            USING (Select DOC_ID,   SRCH_TYP,  CONCEPT_GRP, TRIGGER_TXT, SRCH_TERM_ID
                                   From B01) B
           ON (A.FINDG_KEY = B.DOC_ID or A.FINDG_TYP = B.SRCH_TYP or A.CONCEPT = B.CONCEPT_GRP or A.TRIGGER_TXT = B.TRIGGER_TXT or A.SRCH_TERM_ID = B.SRCH_TERM_ID)
       When Not Matched Then
          INSERT INTO A01 (FINDG_KEY, FINDG_TYP,  CONCEPT,       TRIGGER_TXT,    SRCH_TERM_ID,   KEY_TYP,    RUN_DATE)
                            VALUES (B.DOC_ID,  B.SRCH_TYP, B.CONCEPT_GRP, B.TRIGGER_TXT,  B.SRCH_TERM_ID, v_Key_Type, sysdate);
        Commit;

Please help me fix the issue.

Thanks.

Based on the docs:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_9016.htm#SQLRF01606

Where does it show column names after the into table?
Where does it shoe an INTO on the INSERT?

Remove those:

Merge into A01 A
                             USING (Select DOC_ID,   SRCH_TYP,  CONCEPT_GRP, TRIGGER_TXT, SRCH_TERM_ID
                                    From B01) B
            ON (A.FINDG_KEY = B.DOC_ID or A.FINDG_TYP = B.SRCH_TYP or A.CONCEPT = B.CONCEPT_GRP or A.TRIGGER_TXT = B.TRIGGER_TXT or A.SRCH_TERM_ID = B.SRCH_TERM_ID)
        When Not Matched Then
           INSERT (FINDG_KEY, FINDG_TYP,  CONCEPT,       TRIGGER_TXT,    SRCH_TERM_ID,   KEY_TYP,    RUN_DATE)
                             VALUES (B.DOC_ID,  B.SRCH_TYP, B.CONCEPT_GRP, B.TRIGGER_TXT,  B.SRCH_TERM_ID, v_Key_Type, sysdate); 

Select allOpen in new window

Suggestion from Slightwv  worked.

Thanks a lot