Oracle: Insert only unique records into custom record in compound trigger

user_1977903 0
Viewed 65

I am having a compound trigger in the following format. I need to ensure that my custom declaration ints_rows takes only "unique rows". Meaning if the ints_rows already has a record similar to what is being inserted, it should ignore it. More like a SET data structure. How do I do it in oracle? (I am pretty new to oracle, hence I am not great at syntax) I think I have to either change the BULK COLLECT statement or the declaration of int_records but I might be wrong. Any help/hints are much appreciated.

This is my compound trigger code.

CREATE OR REPLACE
TRIGGER MY_COMPOUND_TRIGGER
FOR UPDATE OF some_random_column ON some_random_table
COMPOUND TRIGGER
  TYPE int_records IS RECORD (
    column_one another_table.column_one%TYPE,
    column_two another_table.column_two%TYPE
  );

  TYPE row_list IS TABLE OF int_records INDEX BY simple_integer;
  ints_rows row_list;

  BEFORE STATEMENT IS
  BEGIN
    ints_rows.delete;
  END BEFORE STATEMENT;

  AFTER EACH ROW IS
  BEGIN
    SELECT column_one, column_two BULK COLLECT INTO ints_rows 
      FROM some_table_x WHERE some_col_id=:OLD.some_col_id;
  END AFTER EACH ROW;

  AFTER STATEMENT IS
  BEGIN
    FOR i IN 1 .. ints_rows.COUNT LOOP
      -- DO SOMETHING
    END LOOP;

    auctions_rows.delete;
  END AFTER STATEMENT;
END;

This statement should not be getting duplicates at all.

SELECT column_one, column_two BULK COLLECT INTO ints_rows 
  FROM some_table_x WHERE some_col_id=:OLD.some_col_id;
triggers plsql stored-procedures oracle database
1 Answers

In the AFTER EACH ROW part you overwrite entire ints_rows. In principle it could be this one:

CREATE TABLE SOME_RANDOM_TABLE (some_random_column NUMBER, some_col_id NUMBER);
CREATE TABLE ANOTHER_TABLE (column_one NUMBER, column_two NUMBER);
CREATE TABLE SOME_TABLE_X (some_col_id NUMBER, column_one NUMBER, column_two NUMBER);

CREATE OR REPLACE TYPE int_records AS OBJECT (
  column_one NUMBER,
  column_two NUMBER,
  MAP MEMBER FUNCTION getId RETURN VARCHAR2
);

CREATE OR REPLACE TYPE BODY int_records AS 
  MAP MEMBER FUNCTION getId RETURN VARCHAR2 IS
  BEGIN
     RETURN column_one ||','|| column_two;
  END getId;
END;
/

CREATE OR REPLACE TYPE row_list IS TABLE OF int_records; 


CREATE OR REPLACE TRIGGER MY_COMPOUND_TRIGGER
FOR UPDATE OF some_random_column ON SOME_RANDOM_TABLE
COMPOUND TRIGGER
      
  ints_rows row_list;

  BEFORE STATEMENT IS
  BEGIN
    ints_rows := row_list();
  END BEFORE STATEMENT;


  AFTER EACH ROW IS
    int_row row_list;
  BEGIN
    SELECT int_records(column_one, column_two) 
    BULK COLLECT INTO int_row 
    FROM SOME_TABLE_X  x
    WHERE x.some_col_id = :OLD.some_col_id;
    ints_rows := ints_rows MULTISET UNION DISTINCT int_row;
    
  END AFTER EACH ROW;

  AFTER STATEMENT IS
  BEGIN
    FOR i IN 1 .. ints_rows.COUNT LOOP
      NULL;
    END LOOP;

  END AFTER STATEMENT;

END;

Please test and let us know if it does not work. You may make the records distinct manually with a loop or you need to implement a MAP MEMBER FUNCTION for the RECORD.

user_30272660
Related