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!

Associate array in compound Triggers

User_W38VZSep 15 2022 — edited Sep 15 2022

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.

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