2 Replies Latest reply: Dec 1, 2004 1:21 PM by 429125 RSS

    AFTER INSERT OR UPDATE TRIGGER

    429125
      All of my tables have a dateTime field which is used to track when a record was inserted/updated. I would like a trigger on each table that updates the dateTime field with the current date and time after each insert or update. I keep getting a mutating error and I can't quit wrap my brain around how to fix it. Could anyone provide a example.

      I know I am getting the error because I am trying to update the row that is currently being inserted or updated - what is the best way to handle this?

      Thanks
        • 1. Re: AFTER INSERT OR UPDATE TRIGGER
          Nicolas.Gasparotto
          Hi,

          A trigger before insert or update is better for your case :
          For example :

          SQL> desc tab_param
          DELAI_RETENTION_FLUX NOT NULL NUMBER(5)
          DATE_DEBUT_ALARME NOT NULL DATE
          DATE_DEBUT_HITSTORIQUE NOT NULL DATE
          NB_FLUX_TOTAL NOT NULL NUMBER(5)
          NB_FLUX_PAGE NOT NULL NUMBER(5)
          DATE_COL DATE

          CREATE OR REPLACE TRIGGER TEST_TRG BEFORE INSERT OR
          UPDATE OF DATE_DEBUT_ALARME, DATE_DEBUT_HITSTORIQUE,
          DELAI_RETENTION_FLUX, NB_FLUX_PAGE, NB_FLUX_TOTAL
          ON TAB_PARAM REFERENCING OLD AS old NEW AS new
          FOR EACH ROW
          begin
          :new.date_col:=sysdate;
          end;

          Nicolas.
          • 2. Re: AFTER INSERT OR UPDATE TRIGGER
            429125
            Nicolas - Thank You. That is exactly what I needed.