Before Insert Trigger !!!!!!! [message #18627] |
Thu, 07 February 2002 16:30 |
Rm69
Messages: 39 Registered: January 2002
|
Member |
|
|
How do l write a trigger on a table to ignore duplicates during an insert.
Have tab1 as the source with col 1,Col2,Col3 etc
l'm inserting new records from tab1 into tab2 which has the same structure as tab1 on a daily basis. How will l ensure this using a trigger that when l run my insert proc duplicates are excluded???
|
|
|
Re: Before Insert Trigger !!!!!!! [message #18631 is a reply to message #18627] |
Thu, 07 February 2002 17:56 |
seng
Messages: 191 Registered: February 2002
|
Senior Member |
|
|
You can check the tab2 before insert into tab2 in trigger (PL/SQL before insert trigger). But you will have performance problem if your table are huge. to reduce performance issue, you need to
1. tuning DML statement in your insert trigger.
2. indexes these two tables.
Hope this will help you. thanks
|
|
|
Re: Before Insert Trigger !!!!!!! [message #18634 is a reply to message #18627] |
Thu, 07 February 2002 22:31 |
Rm69
Messages: 39 Registered: January 2002
|
Member |
|
|
l'm battling trying to get this trigger right so that it checks tab_a if the record exists and if it does it should ignore it.So it should ignore duplicates.How can l achieve this.
CREATE trigger CheckDuplicates
before
insert
ON tab_b
BEGIN
insert INTO tab_b(acc_no, acc_name,summary_date,change_date )
select acc_no,acc_name,summary_date,change_date);
END
|
|
|
Re: Before Insert Trigger !!!!!!! [message #19220 is a reply to message #18634] |
Tue, 05 March 2002 15:44 |
seng
Messages: 191 Registered: February 2002
|
Senior Member |
|
|
Here is same sample ..
CREATE trigger CheckDuplicates before insert ON tab_b
DECLARE
CURSOR c1 SELECT * from tab_a where <your condition>;
BEGIN
OPEN c1;
LOOP
if c1%found then
insert INTO tab_b(acc_no, cc_name,summary_date,change_date )
select acc_no,acc_name,summary_date,change_date);
end if;
EXIT WHEN C1%NOTFOUND;
END LOOP;
END
if you don't want to use the cursor then you can use this DML .
CREATE trigger CheckDuplicates before insert ON tab_b
DECLARE
counttab_a number;
BEGIN
select count(*) into counttab_a from tab_a
where <condition>
if counttab_a = 0 then
insert INTO tab_b(acc_no, cc_name,summary_date,change_date )
select acc_no,acc_name,summary_date,change_date);
end if;
END
Note: this method is more faster the cursor.
Hope this helping. Thanks
|
|
|