CODEWITHSUNDEEP
sqloracle-databaseplsqloracle10g
Shubham Yadav
Shubham Yadav

Reputation: 63

How to use trigger to insert a new row in same table (Oracle PL/SQL 10G)?

My table structure is like this:

ID   NAME   DESG     START_DT     END_DT      CURRENT_FLAG
101  John   Trainee  01-Jan-2016  01-08-2017  Yes

Now I want to update this record (change in Designation) & at the same time his old history should be inserted as a new row. Somewhat like this:

ID   NAME   DESG       START_DT      END_DT       CURRENT_FLAG
101  John   Trainee    01-01-2016   01-08-2017    No
102  John   Associate  02-08-2016   01-01-2099    Yes

I tried using Trigger with following code but this returned me some error.

CODE

CREATE or REPLACE TRIGGER TRI1
BEFORE UPDATE OF type ON TB818 
FOR EACH ROW
BEGIN 
INSERT INTO TB818
VALUES 
(:old.id,
 :old.name, 
 :old.desg, 
 :old.start_dt,
 :old.end_dt,
 :old.current_flag);
END;

----------------------------

> Update TB818 Set Desg='G' where ID=1

ERROR:

ORA-04091: table SYSTEM.TB818 is mutating, trigger/function may not see it
ORA-06512: at "SYSTEM.TRI1", line 2
ORA-04088: error during execution of trigger 'SYSTEM.TRI1'
1. Update TB818 Set type='G' where Id=1

How can I achieve this ?

Upvotes: 2

Views: 2480

Answers (1)

krokodilko
krokodilko

Reputation: 36097

I recommend you to use a separate table for storing history records.
You avoid the mutating table error, and - the most important - your data model will be clear and easy to understand, and you will avoid data corruption when somone will forget to add AND CURRENT_FLAG ='Yes' clause to the update query.

If you must use the same table, then you can use a compound trigger.
This is an example how it can look like - assumming that ID is identity column or is autogenerated using some sequence + trigger.

Sample data:

CREATE TABLE TB818(
    ID  NUMBER GENERATED ALWAYS AS IDENTITY,
    NAME  varchar2(10),
    DESG   varchar2(10),  
    START_DT   date,  
    END_DT date,     
    CURRENT_FLAG varchar2(3) DEFAULT 'Yes' check( CURRENT_FLAG IN ('Yes','No')) 
)
;

INSERT INTO TB818(  NAME ,DESG,START_DT,END_DT ) VALUES('John', 'Trainee', sysdate, sysdate+20);
INSERT INTO TB818(  NAME ,DESG,START_DT,END_DT ) VALUES('Mark', 'Associate', sysdate, sysdate+20);

commit;

A trigger:

set define off;
CREATE OR REPLACE TRIGGER TRI1
FOR UPDATE OF DESG ON TB818 
COMPOUND TRIGGER

   TYPE TB818_ROW_T IS TABLE OF TB818%ROWTYPE;
        TB818_ROWS TB818_ROW_T;
    BEFORE STATEMENT IS
    BEGIN
       TB818_ROWS := TB818_ROW_T();
    END BEFORE STATEMENT;

    BEFORE EACH ROW IS
       TB818_ROW TB818%ROWTYPE;
    BEGIN
       TB818_ROW.NAME := :old.name;
       TB818_ROW.DESG := :old.DESG;
       TB818_ROW.START_DT := :old.START_DT;
       TB818_ROW.END_DT := :old.END_DT;
       TB818_ROWS.EXTEND;
       TB818_ROWS( TB818_ROWS.last ) := TB818_ROW;
    END BEFORE EACH ROW;

    AFTER STATEMENT IS
    BEGIN
       FORALL x IN TB818_ROWS.First .. TB818_ROWS.Last
       INSERT INTO TB818(  NAME ,DESG,START_DT,END_DT, CURRENT_FLAG )
              VALUES( TB818_ROWS( x ).name, TB818_ROWS( x ).desg,
                      TB818_ROWS( x ).start_dt, TB818_ROWS( x ).end_dt,
                      'No'
              );
    END AFTER STATEMENT;
END;
/

Test cases that show what could be if someone who isn't aware of this "feature" that the history rows are stored in the same table and doesn't know that he must always append AND CURRENT_FLAG ='Yes' to all the UPDATE queries - after a few hundreds of updates a system becomes slow, then 'out of memory' error will appear, eventually a tablespace will be full and the system will crash.
This is not a fault of this person who issued these updates - this is a bad, not intuitive design that mislead people and makes the system sensitive.

SELECT * FROM TB818;

        ID NAME       DESG       START_DT         END_DT           CUR
---------- ---------- ---------- ---------------- ---------------- ---
         1 John       Trainee    2017/09/08 09:21 2017/09/28 09:21 Yes
         2 Mark       Associate  2017/09/08 09:21 2017/09/28 09:21 Yes

UPDATE TB818 SET DESG = 'XXX';
SELECT * FROM TB818;

        ID NAME       DESG       START_DT         END_DT           CUR
---------- ---------- ---------- ---------------- ---------------- ---
         1 John       XXX        2017/09/08 09:21 2017/09/28 09:21 Yes
         2 Mark       XXX        2017/09/08 09:21 2017/09/28 09:21 Yes
         3 John       Trainee    2017/09/08 09:21 2017/09/28 09:21 No 
         4 Mark       Associate  2017/09/08 09:21 2017/09/28 09:21 No 

UPDATE TB818 SET DESG = 'ZZZ';
SELECT * FROM TB818;

        ID NAME       DESG       START_DT         END_DT           CUR
---------- ---------- ---------- ---------------- ---------------- ---
         1 John       ZZZ        2017/09/08 09:21 2017/09/28 09:21 Yes
         2 Mark       ZZZ        2017/09/08 09:21 2017/09/28 09:21 Yes
         3 John       ZZZ        2017/09/08 09:21 2017/09/28 09:21 No 
         4 Mark       ZZZ        2017/09/08 09:21 2017/09/28 09:21 No 
         5 John       XXX        2017/09/08 09:21 2017/09/28 09:21 No 
         6 Mark       XXX        2017/09/08 09:21 2017/09/28 09:21 No 
         7 John       Trainee    2017/09/08 09:21 2017/09/28 09:21 No 
         8 Mark       Associate  2017/09/08 09:21 2017/09/28 09:21 No 

UPDATE TB818 SET DESG = 'ABCD';
SELECT * FROM TB818;

        ID NAME       DESG       START_DT         END_DT           CUR
---------- ---------- ---------- ---------------- ---------------- ---
         1 John       ABCD       2017/09/08 09:21 2017/09/28 09:21 Yes
         2 Mark       ABCD       2017/09/08 09:21 2017/09/28 09:21 Yes
         3 John       ABCD       2017/09/08 09:21 2017/09/28 09:21 No 
         4 Mark       ABCD       2017/09/08 09:21 2017/09/28 09:21 No 
         5 John       ABCD       2017/09/08 09:21 2017/09/28 09:21 No 
         6 Mark       ABCD       2017/09/08 09:21 2017/09/28 09:21 No 
         7 John       ABCD       2017/09/08 09:21 2017/09/28 09:21 No 
         8 Mark       ABCD       2017/09/08 09:21 2017/09/28 09:21 No 
         9 John       ZZZ        2017/09/08 09:21 2017/09/28 09:21 No 
        10 Mark       ZZZ        2017/09/08 09:21 2017/09/28 09:21 No 
        11 John       ZZZ        2017/09/08 09:21 2017/09/28 09:21 No 
        12 Mark       ZZZ        2017/09/08 09:21 2017/09/28 09:21 No 
        13 John       XXX        2017/09/08 09:21 2017/09/28 09:21 No 
        14 Mark       XXX        2017/09/08 09:21 2017/09/28 09:21 No 
        15 John       Trainee    2017/09/08 09:21 2017/09/28 09:21 No 
        16 Mark       Associate  2017/09/08 09:21 2017/09/28 09:21 No 

UPDATE TB818 SET DESG = 'QWERTY';
SELECT count(*) FROM TB818;
  COUNT(*)
----------
        32


UPDATE TB818 SET DESG = 'wwww';
SELECT count(*) FROM TB818;

  COUNT(*)
----------
        64

Upvotes: 1

Related Questions