CODEWITHSUNDEEP
sqloracle-databasetriggersoracle12c
Giacomo Giovannini
Giacomo Giovannini

Reputation: 189

Sql Oracle 12c Trigger

I need some help. I'm trying to create a Trigger that execute a procedure whenever insert, delete or update operations are done on a specific table. This is the trigger

CREATE OR REPLACE NONEDITIONABLE TRIGGER NQDI.GAV_TRG 
AFTER INSERT or UPDATE or DELETE ON D_GAV
FOR EACH ROW
BEGIN
    PRC_FILL_D_GAV(:old.report_name);
END;

Unofortunately, since the trigger starts before any commit has been done and I need to read from the same table, it gives me the 'D_GAV table is being modified can't be read' error. Besides, the FOR EACH ROW makes the trigger start for every record changed, while I want the trigger to start only at the end, when every update, insert or delete has been committed, but I haven't find a way to preserve the :old.report_name while doing this. I know I could do what I want with an "up and running process", but I'd like to avoid that. Is there any other solution that I'm overlooking?

Upvotes: 3

Views: 201

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

You want a compound trigger. After each row event you insert the data into an array. And after the statement you loop through the data and call your procedure.

create or replace trigger nqdi.gav_trg
for insert or update or delete on d_gav compound trigger

  type type_table_of_gav is table of d_gav%rowtype;
  v_gavs type_table_of_gav := type_table_of_gav();

  after each row is
  begin
    v_gavs.extend(1);
    v_gavs(v_gavs.count).report_name := coalesce(:old.report_name, :new.report_name);
  end after each row;

  after statement is
  begin
    for i in 1 .. v_gavs.count loop
      prc_fill_d_gav(v_gavs(i).report_name);
    end loop;
  end after statement;

end gav_trg;

Upvotes: 3

Related Questions