Mutating table issue when TRIGGER needs to select and insert rows in same table

Question

We have following requirement :

Table1: composite primary key version, id

------------------------------------
version   id     col1  coll2  active
------------------------------------
 1        123    'A'   'B'     'N'
 2        123    'C'   'D'     'Y'
 1        124    'E'   'F'     'Y'

Now for any insert and update on table1 for a given id, a new row should be created with the following attributes (derived by a trigger):

1. Version should be incremented by 1 for given id and
2. most current row should become active (active column is set to Y)

e.g.

INSERT INTO table1(id, col1, col2) VALUES (123, 'X', 'Y');

------------------------------------
version   id     col1  coll2  active
------------------------------------
 1        123    'A'   'B'     'N'
 2        123    'C'   'D'     'N'
 3        123    'X'   'Y'     'Y' 
 1        124    'E'   'F'     'Y'

3rd row is created

UPDATE table1 SET col1 = 'F' WHERE id = 124;

------------------------------------
version   id     col1  coll2  active
------------------------------------
 1        123    'A'   'B'     'N'
 2        123    'C'   'D'     'N'
 3        123    'X'   'Y'     'Y' 
 1        124    'E'   'F'     'N'
 2        124    'F'   'F'     'Y'

last row is created

DELETE FROM dbo.table1 WHERE id = 124;

------------------------------------
version   id     col1  coll2  active
------------------------------------
 1        123    'A'   'B'     'N'
 2        123    'C'   'D'     'N'
 3        123    'X'   'Y'     'Y' 
 1        124    'E'   'F'     'N'
 2        124    'F'   'F'     'N'

all rows for id 124 become inactive.

This seems to be modeling issue , but we are mandated to provide this feature using TABLE1 and supporting triggers.

We are not able to get around modifying the table issue as we need to select max(version) and then insert into same table, Can anyone please suggest a workaround ?

Answer 1

I don't like the database design, it's horrible, but you say you are stuck with it. How about using a view with INSTEAD OF triggers?

1) Rename the table to e.g. TABLE1_BASE

2) Create view TABLE1 as SELECT * FROM TABLE1_BASE

3) Add an INSTEAD OF trigger like this:

create trigger table1_io
instead of insert or update or delete on table1
for each row
begin
  if inserting or updating then 
      update table1_base
      set active = 'N'
      where id = :new.id
      and active = 'y';

      insert into table1_base...;
  elsif deleting then
      update table1_base
      set active = 'N'
      where id = :old.id
      and active = 'y';
  end if;
end;

(Something like that)

Answer 2

As you say this is a modelling issue and the mutating table error usually indicates that there are better ways to accomplish this.

There are some ways to get around this but none of them are best practices and the added complexity must be weighed against how important the business logic/table is. If this is a key table do you really want to be using complex logic on what should be a simple insert?

Method 1 Put the updates on the table in a package procedure. Run a job from the trigger calling this procedure. This means you now have two transactions: the original insert and the followup.

Method 2 (similar to 1) Create an advanced queue and object type that encapsulates the changes you wish to make. During the insert add to the queue. Dequeue objects and apply changes as required. In your case: immediately. Method 1 and 2 have the advantage that the initial transaction will never be stopped because of the followup cleanup.

Method 3 (works but is not best practice due to the complexity) You need two triggers: an after Insert on the table and after insert on the table for each row

Both triggers call a package encapsulating your logic which will look something like this

   CREATE OR REPLACE PACKAGE BODY XYZ AS
   TYPE t_template_rec
   IS
      RECORD (case_id     NUMBER (10), objective_id NUMBER (10));

   TYPE t_template_table IS TABLE OF t_template_rec;

   g_change_table t_template_table
         := t_template_table () ;

   TYPE t_deliv_rec IS RECORD (case_id NUMBER (10), stage_id NUMBER (10));

   TYPE t_deliv_table IS TABLE OF t_deliv_rec;

   g_deliv_table                         t_deliv_table := t_deliv_table ();

Table level trigger passes a primary key and data to this procedure

   PROCEDURE CHECK_FOR_TEMPLATES (case_id_in     IN NUMBER,
                                  objective_in   IN NUMBER)
   IS

   BEGIN

      g_change_table.EXTEND;
      g_change_table (g_change_table.LAST).case_id := case_id_in;
      g_change_table (g_change_table.LAST).objective_id := objective_in;
   END CHECK_FOR_TEMPLATES;

The row level trigger calls a different procedure in the same package

   PROCEDURE ADD_TEMPLATES_ON_STATEMENT
   IS

      v_count          NUMBER (10);
      v_case_id        NUMBER (10);
      v_objective_id   NUMBER (10);
   BEGIN
      FOR i IN g_change_table.FIRST .. g_change_table.LAST
      LOOP
         SELECT   COUNT ( * )
           INTO   v_count
           FROM   XYZ.DELIVERABLE_TEMPLATE dt
          WHERE   DT.INITIATIVE_OBJECTIVE_ID =
                     g_change_table (i).objective_id;
         --check if there is any work to be done
         IF v_count > 0
         THEN
            v_case_id := g_change_table (i).case_id;
            v_objective_id := g_change_table (i).objective_id;
            --do some work here
         END IF;
      END LOOP;

      g_change_table.delete;
  END ADD_TEMPLATES_ON_STATEMENT;

I have removed comments/debugging/asserts for clarity. I am using Method #3 and once I got it working it has worked fine but if I had to go back and implement more business logic on that table I would approach it with care.