create complex trigger on table

Question

I have a table participants having structure as shown below:

Pid    number        
name   varchar2(20)    
version  number

Whenever i inserted any record in participants table ,version =1 get populated.

For Example ,if i inserted pid=1 ,name='Gaurav' then record with version =1 get populated in participants table .

Now my issue is with update on participants table,

1. Suppose i am updating name ='Niharika' for pid=1 in participants table then a new record with pid=1 ,name='Niharika' and version =2 need to be created on the same table .
2. Again i update name='Rohan' for pid='1' in participants table a new record with pid=1 ,name='Rohan' and version=3 needs to be created .

How can i achieve this , clearly speaking i need to get max(version)+1 for that pid that is going to update .

I can achieve this using view and insert into view using instead of trigger ,but i am not satisfied with my solution .

I have also created compound trigger ,even that is not working for me because inside trigger i need to use insert statement for that table and this will give me recursive error

Answer 1

Not usually recommended, but here's how you can do it anyways with no other extra logging table(s)-

CREATE or REPLACE
TRIGGER part_upd
 AFTER UPDATE of name
 ON participants
 FOR EACH ROW
DECLARE
  retval BOOLEAN;
BEGIN

   retval := insert_row(:old.pid,:new.name);

END part_upd;

The function-

CREATE or REPLACE 
FUNCTION insert_row (pid1 number, name1 varchar2)
RETURN boolean
IS
    PRAGMA autonomous_transaction;
BEGIN
     INSERT INTO participants
     SELECT pid1, name1, max(vers)+1
       FROM participants
      WHERE pid = pid1;         

     COMMIT; 

     RETURN true;
END;

You'll have to fine tune the Trigger and Function properly by adding logging and exception handling. Read more about autonomous_transaction.

Answer 2

You should really have two tables. Make one with the structure you described as a "logging" table. It will keep the history of all the records. Have another table which is considered "current" which is the same but without the version column. Then, when inserts/update occur on the "current" tables' records, have a mechanism (trigger, for example) SELECT FOR UPDATE the max(version) in the logging table, add one, and insert into the logging table. This way, you're not going to run into mutating table errors or anything weird like that. There is a bit of serialization this way, but it's the closest to what you're trying to do.