Home » SQL & PL/SQL » SQL & PL/SQL » Before Insert Trigger !!!!!!!
Before Insert Trigger !!!!!!! [message #18627] Thu, 07 February 2002 16:30 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: which is best ?
Next Topic: how to copy table content from one to another with order
Goto Forum:
  


Current Time: Thu Mar 28 15:58:01 CDT 2024