Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Trigger to find the no. of duplicates in a table and insert them into new table.

User_W38VZSep 15 2022

Hi,
I'm new to trigger , collections and records.
I wanted to solve the below situation using a trigger. The table_a, when ever it gets new duplicate rows inserted i wanted those duplicates to be inserted in table_b as well.

So I tried the below trigger but ended up getting mutating table error.

create or replace TRIGGER table_one_dup_check
before INSERT on table_1
FOR EACH ROW
DECLARE
v_dup number;
 
BEGIN
     SELECT count(*) INTO v_dup
       FROM table_1 a
      WHERE a.id= :NEW.id
        AND a.mark = :NEW.mark
        AND a.class = :NEW.class
        AND a.date = :NEW.date;

    IF v_dup > 0 THEN
    INSERT INTO table_2 (id,mark,class,date,exam_date)
    VALUES (:NEW.id,:NEW.mark,:NEW.class,:NEW.date,:NEW.exam_date);
    END IF;
END;


create or replace TRIGGER table_1_dup_delete
AFTER INSERT on table_1
DECLARE
BEGIN
DELETE FROM table_1 a
WHERE (a.id,a.mark,a.class,a.date,a.exam_date) IN (SELECT 
        b.id,b.mark,b.class,b.date,b.exam_date FROM table_2 b); 

END;

To solve this i thought of using compound trigger with records and associative array. but i get compliation error.

EATE OR REPLACE TRIGGER dup_check
FOR INSERT ON table_1
COMPOUND TRIGGER



TYPE r_type IS RECORD (
v_class NUMBER,
v_mark VARCHAR2(1),
v_id VARCHAR2(36),
v_date DATE,
v_exam_Date  TIMESTAMP(6)
);



TYPE a_records IS TABLE OF r_type INDEX BY varchar(40);



new_records a_records;



v_dup number;



BEFORE EACH ROW IS
BEGIN
new_records(1) := :NEW.class;
new_records(2) := :NEW.id ;
new_records(3) := :NEW.mark ;
new_records(4) := :NEW.date ;
new_records(5) := :NEW.exam_Date;



END BEFORE EACH ROW;



AFTER STATEMENT IS
   BEGIN
      SELECT count(*) INTO v_dup
       FROM table_1 a
      WHERE a.class = new_records(1)
        AND  a.id = new_records(2)
        AND a.mark = new_records(3)
        AND a.date = new_records(4);



     FOR i IN 1..v_dup
      LOOP
         INSERT INTO table_2 (class,id,mark,date,exam_date)
    VALUES (new_records(1),new_records(2),new_records(3),new_records(4),new_records(5));
      END LOOP;
   END AFTER STATEMENT;
END;

Can someone please help me fix this issue using COMPOUNG TRIGGER , please.

This post has been answered by Frank Kulash on Sep 15 2022
Jump to Answer
Comments
Post Details
Added on Sep 15 2022
9 comments
943 views