Mutating Trigger Oracle

Question

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

Answer 1

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

Answer 2

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>