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.