CODEWITHSUNDEEP
oracle-databaseplsqltriggersoracle12cmutating-table
Anders
Anders

Reputation: 8577

Copy row to history table before update without enumerating column names in PL/SQL trigger?

I am trying to create a PL/SQL trigger that copies the current version of a row into a history table when the row is updated. This can be easily done like this:

CREATE OR REPLACE TRIGGER foo BEFORE UPDATE ON bar FOR EACH ROW
BEGIN
  INSERT INTO bar_history VALUES bar.id = :old.id, bar.col1 = :old.col1 /* ...and so on */;
END;

However, I would like to avoid enumerating all the column names since the tables bar and bar_history are identical and I don't want to update the trigger every time I change the table. I have tried two approaches, none of which are working. Is there any other way to solve this?

Approach 1:

CREATE OR REPLACE TRIGGER foo BEFORE UPDATE ON bar FOR EACH ROW
BEGIN
  INSERT INTO bar_history VALUES :old;
END;

Since you apparently can not use :old as a rowtype (see this question) I get the following error message:

PLS-00049: bad bind variable 'OLD'

Approach 2:

CREATE TRIGGER foo BEFORE UPDATE ON bar FOR EACH ROW
BEGIN
  INSERT INTO bar_history
  SELECT * FROM bar WHERE id = :old.id;
END;

This also gives me an error:

ORA-04091: BAR is mutating, trigger/function may not see it

Upvotes: 1

Views: 2857

Answers (3)

jva
jva

Reputation: 2805

Some people have run into this situation before. Their approach - use package global variables to store rowid and dynamically select column names in after statement trigger. https://community.oracle.com/message/370167

Tom Kyte also has some advice on this

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:734825535375 https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:59412348055

Upvotes: 1

Koen Lostrie
Koen Lostrie

Reputation: 18685

You need to reference the individual columns. There is no way to reference the entire row in the trigger. If you need this for many tables or your table definition changes frequently, you could write pl/sql based on the data dictionary views to generate the trigger for you. Update: Similar question/answer here: In an Oracle trigger, can I assign new and old to a rowtype variable?

Upvotes: 2

Durga Viswanath Gadiraju
Durga Viswanath Gadiraju

Reputation: 3956

In this case you can create partition table using list partitioning strategy (one for current C and history H). You can create instead of trigger, in case of update of the table, record will be inserted and existing record status will be updated to H - which implicitly move the record to historical partition.

Upvotes: 0

Related Questions