Trigger for inserting only new records in an existing table

Hi Experts,

Say, Table A01 has 100 records in 5 columns, col1, col2, col3, col4, col5 The datatype are varchar2, date, number etc. (No clob or blob)

Table B01 has same columns and datatype as A01. Via  a procedure, Table B01 is truncated and it gets new vales when the procedure executes.

These vales from B01 are to be inserted into A01 WITHOUT Creating any duplicate records in A01. In other words,  a record gets inserted into A01 ONLY IF  no value in any of the columns already exist in table A01.  

How would you do that?

Thanks a lot.
Kamal AgnihotriAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
A couple of ways off the top of my head.  There are likely more.

1:  select first.  If nothing is found, then do the insert.

2:  create a unique index on ALL columns and trap the dup_val_on_index exception when you attempt the insert.
Kamal AgnihotriAuthor Commented:
Hi Slightwv,

Please provide code for implementing your suggestion 1. Would it be a trigger, or a procedure?? I would prefer a trigger. Thanks.
slightwv (䄆 Netminder) Commented:
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;
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Mark GeerlingsDatabase AdministratorCommented:
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.
slightwv (䄆 Netminder) Commented:
>>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?
Mark GeerlingsDatabase AdministratorCommented:
This looks to me like syntax that is likely to hit the "mutating table" error, because it directly refers to values in the triggering table in a SQL statement or query:

"select count(*) into some_variable from a01 where col1=:new.col1 ..."
slightwv (䄆 Netminder) Commented:
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.
Kamal AgnihotriAuthor Commented:
Hi Slightwv/markgeer,

Thanks.

In my environment, we are unlikely to experience "mutating tables" issue.  Records in Table B01 are INSTERTED, not UPDATED.
slightwv (䄆 Netminder) Commented:
>>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.
Mark GeerlingsDatabase AdministratorCommented:
"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 ..."
slightwv (䄆 Netminder) Commented:
>>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.
Geert GOracle dbaCommented:
you truncate B01 ...
why make it so difficult ?

insert into B01
select distinct * from A01
slightwv (䄆 Netminder) Commented:
>>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.
awking00Information Technology SpecialistCommented:
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;
/
awking00Information Technology SpecialistCommented:
>>a record gets inserted into A01 ONLY IF  no value in any of the columns already exist in table A01.<<
I missed this part, so the trigger needs to be modified to -
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 or a.col2 = b.col2 or a.col3 = b.col3 or a.col4 = b.col4 or a.col5 or b.col5)
 when not matched then
 insert values(b.col1, b.col2, b.col3, b.col4, b.col5)';
 execute immediate v_sql;
 end;
slightwv (䄆 Netminder) Commented:
-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.
awking00Information Technology SpecialistCommented:
slightwv,
You're right (as usual). I seems to me that when the merge function was introduced initially, a commit statement was automatically generated and I tended to think of it like DDL. At any rate, it does make the trigger shorter to write.
create or replace trigger insert_a01
 after insert on b01
 begin
 merge into a01 a using b01 b on (a.col1 = b.col1 or a.col2 = b.col2 or a.col3 = b.col3 or a.col4 = b.col4 or a.col5 or b.col5)
 when not matched then insert values(b.col1, b.col2, b.col3, b.col4, b.col5);
commit; ==> this should have been in the earlier version as well
 end;
As far as the merge vs. where rownum = 1 is concerned, I don't think with only a hundred records there would not be much difference if any. However, with a lot of rows a statement trigger (i.e. the merge) will usually outperform a trigger for each row (which I assume the where rownum = 1 trigger uses).
slightwv (䄆 Netminder) Commented:
>> 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.
Kamal AgnihotriAuthor Commented:
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.
Kamal AgnihotriAuthor Commented:
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.
slightwv (䄆 Netminder) Commented:
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); 

Open in new window

Kamal AgnihotriAuthor Commented:
Suggestion from Slightwv  worked.

Thanks a lot
slightwv (䄆 Netminder) Commented:
Although I appreciate the points, I shouldn't get all of them...

I only corrected the syntax I didn't suggest the MERGE.  awking00 first suggested MERGE as a solution and should get the points.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.