CODEWITHSUNDEEP
oracle-databasetriggersmutating-table
user3110283
user3110283

Reputation: 11

Mutating Trigger Oracle

I have two tables main and hist. I want to copy a row from main table whenever its datetime column is modified to hist and reset the modified colums in the main to null except one column. However I am getting mutating trigger error. Please help. Below are the two triggers, 

CREATE OR REPLACE TRIGGER INS_HIS
AFTER UPDATE OF datetime ON main
FOR EACH ROW 
  INSERT INTO HIST 
    VALUES (Col2 = :new.col2, Col3= :new.Col3)
END;


CREATE OR REPLACE TRIGGER UPD_NUL_MAIN
AFTER INSERT ON HIST
FOR EACH ROW
   UPDATE Main 
      SET (Col2 = NULL
           Col3= NULL)
    WHERE HIST.datetime = main.datetime;
END

Upvotes: 1

Views: 166

Answers (2)

Rob Baillie
Rob Baillie

Reputation: 3460

In order to do this I think you only need one trigger?

CREATE OR REPLACE TRIGGER ins_his BEFORE UPDATE OF datetime ON main
FOR EACH ROW 
BEGIN

  INSERT INTO hist ( col2     , col3      )
            VALUES ( :new.col2, :new.col3 );

  :new.col2 := NULL;
  :new.col3 := NULL;

END;
/

Example output:

SQL*Plus: Release 11.2.0.1.0 Production on Tue Dec 17 13:17:08 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management,
OLAP,
Data Mining and Real Application Testing options

SQL> create table main ( datetime DATE, col2 NUMBER, col3 NUMBER );

Table created.

SQL> create table hist ( col2 NUMBER, col3 NUMBER );

Table created.

SQL> CREATE OR REPLACE TRIGGER ins_his BEFORE UPDATE OF datetime ON main
  2  FOR EACH ROW
  3  BEGIN
  4    --
  5    INSERT INTO hist ( col2     , col3      )
  6              VALUES ( :new.col2, :new.col3 );
  7    --
  8    :new.col2 := NULL;
  9    :new.col3 := NULL;
 10    --
 11  END;
 12  /

Trigger created.

SQL> insert into main( datetime, col2, col3 )
  2  values ( sysdate, 5, 10 );

1 row created.

SQL> select * from main;

DATETIME        COL2       COL3
--------- ---------- ----------
17-DEC-13          5         10

SQL> select * from hist;

no rows selected

SQL> update main set datetime = sysdate-1;

1 row updated.

SQL> select * from main;

DATETIME        COL2       COL3
--------- ---------- ----------
16-DEC-13

SQL> select * from hist;

      COL2       COL3
---------- ----------
         5         10

SQL>

Upvotes: 1

Frank Schmitt
Frank Schmitt

Reputation: 30765

Assuming you want to change only the row in main that triggered the update, you can get rid of the second trigger altogether if you change your first trigger to a BEFORE UPDATE one and set col2 and col3 there:

CREATE OR REPLACE TRIGGER INS_HIS
BEFORE UPDATE OF datetime ON main
FOR EACH ROW          
BEGIN
  INSERT INTO HIST
    (col2,
     col3)
  VALUES
    (:new.col2,
     :new.Col3);
  :new.col2 := NULL;
  :new.col3 := NULL;
END;

BTW: your trigger syntax is wrong - you cannot use (col2 = :new.col2).

Upvotes: 2

Related Questions