I want to find out the duplicate values that are being inserted in test_1 table and insert them into test_2 table.
For this purpose i created a compound trigger (to avoid the mutate table issue i have used the compound trigger here) with associate array variable in it.
create or replace TRIGGER dup_ct
FOR INSERT ON test_1
COMPOUND TRIGGER
TYPE r_type IS RECORD (v_class_id varchar2(1),v_mark VARCHAR2(10),v_std VARCHAR2(36),v_res_Date DATE,v_exam_date TIMESTAMP(6));
TYPE a_records IS TABLE OF r_type index by pls_interger;
new_records a_records := a_records();
v_dup number;
BEFORE EACH ROW IS
BEGIN
new_records(1) := r_type (:new.class_id,:new.mark,:new.std,:new.res_date,:new.exam_Date);
END BEFORE EACH ROW;
AFTER STATEMENT IS
BEGIN
SELECT count(*) INTO v_dup
FROM test_1 a
WHERE a.class_id = new_records(1).v_class_id
AND a.mark = new_records(1).v_mark
AND a.std = new_records(1).v_std
AND a.res_date = new_records(1).v_res_Date ;
FOR i IN 1..v_dup
LOOP
INSERT INTO test_2 (class_id,mark,std,res_date,exam_Date)
VALUES (new_records(1).v_class_id,v_mark(1).v_mark,new_records(1).v_std,new_records(1).v_res_Date,new_records(1).v_exam_date);
END LOOP;
END AFTER STATEMENT;
END;
Single row insert works fine with this code. But with multiple value insert the values are getting inserted in the test_1 table but only the last row values are inserted in the test_2 table. since the array is only using tuple(1). how do i find the count of all the values that are being inserted and store them in array and use them to insert the values in the test_2 table?
Here is the query to re-create the issue :
CREATE TABLE test1 (
class_id number primary key,
mark varchar2(10),
std varchar2(1),
res_date date,
exam_Date timestamp(6)
);
create table test2 as select * from test 1 where 1=2;
create table test3 as select * from test 1 where 1=2;
1. insert into test1 values (1,'rank 1','vii',to_date('01-02-22','dd-mm-yy'),to_timestamp('10-01-22 13:30:00,000000000','dd-mm-yy hh24:mi:ss,ff9');
2. insert into test1 values (2,'rank 2','vi',to_date('01-02-22','dd-mm-yy'),to_timestamp('10-01-22 13:30:00,000000000','dd-mm-yy hh24:mi:ss,ff9');
so the single inserts works fine.
1. insert into test3 values (3,'rank 1','vi',to_date('01-02-22','dd-mm-yy'),to_timestamp('10-01-22 13:30:00,000000000','dd-mm-yy hh24:mi:ss,ff9');
2. insert into test3 values (6,'rank 2','vii',to_date('01-02-22','dd-mm-yy'),to_timestamp('10-01-22 13:30:00,000000000','dd-mm-yy hh24:mi:ss,ff9');
3. insert into test3 values (4,'rank 4','iv',to_date('01-02-22','dd-mm-yy'),to_timestamp('10-01-22 13:30:00,000000000','dd-mm-yy hh24:mi:ss,ff9');
4. insert into test3 values (5,'rank 5','ii',to_date('01-02-22','dd-mm-yy'),to_timestamp('10-01-22 13:30:00,000000000','dd-mm-yy hh24:mi:ss,ff9');
insert into test1 (class_id,mark,std,res_date,exam_Date)
select (class_id,mark,std,res_date,exam_Date from test3);
when i try to do multiple inserts the values from line 1 and line 2 of inserting in test3 table already exisits in the test1 table.
so i would expect these two to be inserted in the test2 table via the trigger. but only the line2 is being inserted.
i wanted the combination of mark,class,date,exam_date to be unique. if they are not unique i wanted these values to be inserted in the test_2 table as well.