Update previous record version during Oracle insert trigger

2
votes

This is my insert trigger on Table_A where I store parameters to my system. When I do insert to the table, I want to change end_date of last record in order to keep record versioning.

create or replace trigger parameter_version
  before insert
  on parameters
  for each row
declare
  v_is_exist number := 0;
  v_rowid rowid;
begin  
  select count(*) into v_is_exist from parameters where name = :new.name; -- check if parameter exist
  select rowid into v_rowid from parameters where name = :new.name and end_date is null; -- record rowid, which sholud be changed    
  if v_is_exist <> 0 then     
    set end_date = :new.start_date - 1
  end if;
end;

Situation in table before insert is:

| id | name | value | start_date |  end_date  |
-----------------------------------------------
| 1  |Par_A |   10  | 2016-09-01 | 2016-10-01 |
-----------------------------------------------
| 2  |Par_A |   20  | 2016-10-02 | 2016-10-03 |
-----------------------------------------------
| 3  |Par_A |   30  | 2016-10-05 |   <null>   |
-----------------------------------------------

Record with id=3 should set end_date on :new_start_date - 1 (close version) and in inserting record I have a next param version with start_date = sysdate.

I've got an ORA-04091 error 'table name is mutating, trigger/function may not see it'.

I know that this case is hard and probably impossible but maybe someone know the solution? Or maybe exists another solution that case?

2
sqloracleplsqloracle11g
This isn't a good candidate for using a trigger. As the error message suggests, the contents of the table are still in flux and the trigger may not be firing after all relevant rows have been updated. Much better to have the calling code make the necessary updates. If this is a firm requirement, you would be best served researching Compound Triggers - Chris Hep
Dependent on the version of your Oracle database have a look at compound triggers (only as a last resort, I think you should revise your design) - sers
You need to use a statement level trigger. Row level triggers can't modify the table being updated. - a_horse_with_no_name

2 Answers

1
votes

You can handle this with an After Statement trigger with the LEAD Analytic Function:

DROP TABLE demo;

CREATE TABLE demo( id          NUMBER
                 , name        VARCHAR2( 30 )
                 , VALUE       NUMBER
                 , start_date  DATE
                 , end_date    DATE
                  );

INSERT INTO demo( id, name, VALUE, start_date, end_date )
     VALUES ( 1, 'Par_A', 10, TO_DATE( '2016-09-01', 'YYYY-MM-DD' ), TO_DATE( '2016-10-01', 'YYYY-MM-DD' ) );

INSERT INTO demo( id, name, VALUE, start_date, end_date )
     VALUES ( 2, 'Par_A', 20, TO_DATE( '2016-10-02', 'YYYY-MM-DD' ), TO_DATE( '2016-10-04', 'YYYY-MM-DD' ) );

INSERT INTO demo( id, name, VALUE, start_date )
     VALUES ( 3, 'Par_A', 30, TO_DATE( '2016-10-05', 'YYYY-MM-DD' ) );

INSERT INTO demo( id, name, VALUE, start_date )
     VALUES ( 4, 'Par_A', 40, TO_DATE( '2016-10-07', 'YYYY-MM-DD' ) );

INSERT INTO demo( id, name, VALUE, start_date )
     VALUES ( 5, 'Par_A', 50, TO_DATE( '2016-10-11', 'YYYY-MM-DD' ) );

COMMIT;

SELECT   id
       , name
       , start_date
       , end_date
       , LEAD( start_date ) OVER( PARTITION BY name ORDER BY start_date ) - 1 AS new_date
    FROM demo
   WHERE end_date IS NULL
ORDER BY id;

CREATE OR REPLACE TRIGGER demo_aius
    AFTER INSERT OR UPDATE
    ON demo
    REFERENCING NEW AS new OLD AS old
DECLARE
    CURSOR c_todo
    IS
        SELECT id, new_date
          FROM (SELECT id
                     , name
                     , start_date
                     , end_date
                     , LEAD( start_date ) OVER( PARTITION BY name ORDER BY start_date ) - 1 AS new_date
                  FROM demo
                 WHERE end_date IS NULL)
         WHERE new_date IS NOT NULL;
BEGIN
    FOR rec IN c_todo
    LOOP
        UPDATE demo
           SET end_date  = rec.new_date
         WHERE id = rec.id;
    END LOOP;
END demo_aius;
/

INSERT INTO demo( id, name, VALUE, start_date )
     VALUES ( 6, 'Par_A', 60, TO_DATE( '2016-10-15', 'YYYY-MM-DD' ) );

COMMIT;

SELECT   id
       , name
       , start_date
       , end_date
    FROM demo
ORDER BY id;

Like the Script shows, such an Update can even handle multiple missing end dates, in case the trigger was accidentally disabled. The "PARTITION BY name" part makes sure that it also functions after complex insert statements.

BtW I agree that Autonomous Transactions in triggers are a last resort. I try to avoid triggers in general by controlling the User Interface and putting all such functionality in packages.

0
votes

Try something like this:

create or replace trigger parameter_version
   before insert
   on parameters
   for each row
begin
   /*Don't care if there's 0 rows updated */
   update parameters
      set end_date = :new.start_date - 1
    where name = :new.name and end_date is null;

   :new.end_date := null;
end;