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,

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 .
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

anon · Accepted Answer · 2012-07-18 22:51:47Z

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.

answered Jul 18, 2012 at 22:51

anon

Sign up to request clarification or add additional context in comments.

Comments

Anjan Biswas · Accepted Answer · 2012-07-19 03:14:28Z

-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.

edited Jul 19, 2012 at 3:14

answered Jul 19, 2012 at 2:33

Anjan Biswas

8,0126 gold badges51 silver badges78 bronze badges

3 Comments

Jeffrey Kemp Over a year ago

-1 This will result in spurious rows inserted if the triggering statement is rolled back.

Anjan Biswas Over a year ago

That was the reason the first words were "Not usually recommended", there is always a caveat of using "autonomous_transaction"

Jeffrey Kemp Over a year ago

"Not usually recommended" implies that you might "sometimes recommend" this approach. I disagree.

Collectives™ on Stack Overflow

create complex trigger on table

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related