CODEWITHSUNDEEP
triggersoracle10g
Pranay
Pranay

Reputation: 63

use trigger to insert into table if data is not already present


I have two tables with the same structure. Table 1 has multiple rows which can have same values. Now i want to insert the same rows into table 2 excluding duplicate rows. I am able to do this normally using 'minus', but i want to write a trigger such that if a new row is inserted into table 1 and is not present in table 2 then insert in table 2 otherwise not. I am new to triggers. The trigger i have written gives me "trigger is mutating" error when i insert in table 1.

INSERT INTO t3(name1,name2,num1,num2) select name1,name2,num1,num2 from t1 group by name1,name2,num1,num2 minus select * from t3

when i write the above code it works fine but when i include this into my trigger it gives error. How do i perform the above with the help of a trigger?

Please help,

Thanks
Pranay

Upvotes: 0

Views: 389

Answers (1)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60262

You don't need to requery the table from a row-level trigger. That's what the :NEW. syntax is for, e.g.:

INSERT INTO t3(name1,name2,num1,num2)
select :NEW.name1,:NEW.name2,:NEW.num1,:NEW.num2 from DUAL
minus select name1,name2,num1,num2 from t3;

Although I think the above code looks a bit silly. I'd prefer to put a unique constraint on t3 then add a handler in the trigger to take care of any DUP_VAL_ON_INDEX exceptions.

Upvotes: 1

Related Questions