Mutating tables

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • M Mueller
    #1

    Mutating tables

    Hello -

    I'm using Oracle 8i and am running into the infamous mutating table
    error on an AFTER INSERT OR UPDATE FOR EACH ROW trigger. I have seen
    examples for getting around mutating table errors if you either need
    access to the :old or the :new values within a trigger. But what if
    you need access to both? Or is this even possible (I'm fairly new to
    triggers).

    I have to execute a select count(*) from trigger_table where
    value1=:old.val ue1 and value2=:old.val ue2. Based on this result, log
    files must be written utilizing the :new values. A simplified example
    of my trigger is below:

    CREATE OR REPLACE TRIGGER mut_trigger
    AFTER INSERT OR UPDATE OF value1, value2
    ON table_a
    REFERENCING OLD AS OLD NEW AS NEW
    FOR EACH ROW
    DECLARE
    numofdocs number;
    BEGIN
    select count(*) into numofdocs from table_a where value1=:old.val ue1
    and value2=:old.val ue2;
    if (numofdocs 0) then
    insert into log_table1 values ('Log data', :new.value1,
    :new.value2);
    insert into log_table2 values ('Log data', :new.value1,
    :new.value2);
    end if;
    END;

    Is there any way to get what I need using one trigger?

    Thanks so much,
    Melissa
    Tags: None
    • Olaf Maathuis
      #2
      Re: Mutating tables

      Simply placing the select statement in a statement level trigger instead in
      a row level trigger gives the solution. In addition, you need to store the
      old en new values in something like a packaged pl/sql table, which you can
      acces in the statement level trigger. Filling the pl/sql table occurs in the
      row level trigger. Besides, the query is a little bit useless when
      inserting, since in that case old values simply don't exists, i.e. equals
      null (numofdocs whill alwasy equal 0 when inserting).
      "M Mueller" <melissa_muelle r@yahoo.comschr ieb im Newsbeitrag
      news:ac8fbcca.0 402240557.55d69 a39@posting.goo gle.com...
      Hello -
      >
      I'm using Oracle 8i and am running into the infamous mutating table
      error on an AFTER INSERT OR UPDATE FOR EACH ROW trigger. I have seen
      examples for getting around mutating table errors if you either need
      access to the :old or the :new values within a trigger. But what if
      you need access to both? Or is this even possible (I'm fairly new to
      triggers).
      >
      I have to execute a select count(*) from trigger_table where
      value1=:old.val ue1 and value2=:old.val ue2. Based on this result, log
      files must be written utilizing the :new values. A simplified example
      of my trigger is below:
      >
      CREATE OR REPLACE TRIGGER mut_trigger
      AFTER INSERT OR UPDATE OF value1, value2
      ON table_a
      REFERENCING OLD AS OLD NEW AS NEW
      FOR EACH ROW
      DECLARE
      numofdocs number;
      BEGIN
      select count(*) into numofdocs from table_a where value1=:old.val ue1
      and value2=:old.val ue2;
      if (numofdocs 0) then
      insert into log_table1 values ('Log data', :new.value1,
      :new.value2);
      insert into log_table2 values ('Log data', :new.value1,
      :new.value2);
      end if;
      END;
      >
      Is there any way to get what I need using one trigger?
      >
      Thanks so much,
      Melissa

        Comment

        • Olaf Maathuis
          #3
          Re: Mutating tables

          Simply placing the select statement in a statement level trigger instead in
          a row level trigger gives the solution. In addition, you need to store the
          old en new values in something like a packaged pl/sql table, which you can
          acces in the statement level trigger. Filling the pl/sql table occurs in the
          row level trigger. Besides, the query is a little bit useless when
          inserting, since in that case old values simply don't exists, i.e. equals
          null (numofdocs whill alwasy equal 0 when inserting).
          "M Mueller" <melissa_muelle r@yahoo.comschr ieb im Newsbeitrag
          news:ac8fbcca.0 402240557.55d69 a39@posting.goo gle.com...
          Hello -
          >
          I'm using Oracle 8i and am running into the infamous mutating table
          error on an AFTER INSERT OR UPDATE FOR EACH ROW trigger. I have seen
          examples for getting around mutating table errors if you either need
          access to the :old or the :new values within a trigger. But what if
          you need access to both? Or is this even possible (I'm fairly new to
          triggers).
          >
          I have to execute a select count(*) from trigger_table where
          value1=:old.val ue1 and value2=:old.val ue2. Based on this result, log
          files must be written utilizing the :new values. A simplified example
          of my trigger is below:
          >
          CREATE OR REPLACE TRIGGER mut_trigger
          AFTER INSERT OR UPDATE OF value1, value2
          ON table_a
          REFERENCING OLD AS OLD NEW AS NEW
          FOR EACH ROW
          DECLARE
          numofdocs number;
          BEGIN
          select count(*) into numofdocs from table_a where value1=:old.val ue1
          and value2=:old.val ue2;
          if (numofdocs 0) then
          insert into log_table1 values ('Log data', :new.value1,
          :new.value2);
          insert into log_table2 values ('Log data', :new.value1,
          :new.value2);
          end if;
          END;
          >
          Is there any way to get what I need using one trigger?
          >
          Thanks so much,
          Melissa

            Comment

            • Olaf Maathuis
              #4
              Re: Mutating tables

              Simply placing the select statement in a statement level trigger instead in
              a row level trigger gives the solution. In addition, you need to store the
              old en new values in something like a packaged pl/sql table, which you can
              acces in the statement level trigger. Filling the pl/sql table occurs in the
              row level trigger. Besides, the query is a little bit useless when
              inserting, since in that case old values simply don't exists, i.e. equals
              null (numofdocs whill alwasy equal 0 when inserting).
              "M Mueller" <melissa_muelle r@yahoo.comschr ieb im Newsbeitrag
              news:ac8fbcca.0 402240557.55d69 a39@posting.goo gle.com...
              Hello -
              >
              I'm using Oracle 8i and am running into the infamous mutating table
              error on an AFTER INSERT OR UPDATE FOR EACH ROW trigger. I have seen
              examples for getting around mutating table errors if you either need
              access to the :old or the :new values within a trigger. But what if
              you need access to both? Or is this even possible (I'm fairly new to
              triggers).
              >
              I have to execute a select count(*) from trigger_table where
              value1=:old.val ue1 and value2=:old.val ue2. Based on this result, log
              files must be written utilizing the :new values. A simplified example
              of my trigger is below:
              >
              CREATE OR REPLACE TRIGGER mut_trigger
              AFTER INSERT OR UPDATE OF value1, value2
              ON table_a
              REFERENCING OLD AS OLD NEW AS NEW
              FOR EACH ROW
              DECLARE
              numofdocs number;
              BEGIN
              select count(*) into numofdocs from table_a where value1=:old.val ue1
              and value2=:old.val ue2;
              if (numofdocs 0) then
              insert into log_table1 values ('Log data', :new.value1,
              :new.value2);
              insert into log_table2 values ('Log data', :new.value1,
              :new.value2);
              end if;
              END;
              >
              Is there any way to get what I need using one trigger?
              >
              Thanks so much,
              Melissa

                Comment

                • What The !!!
                  #5
                  Re: Mutating tables

                  @#$$#%^%%&^
                  "M Mueller" <melissa_muelle r@yahoo.comwrot e in message
                  news:ac8fbcca.0 402240557.55d69 a39@posting.goo gle.com...
                  Hello -
                  >
                  I'm using Oracle 8i and am running into the infamous mutating table
                  error on an AFTER INSERT OR UPDATE FOR EACH ROW trigger. I have seen
                  examples for getting around mutating table errors if you either need
                  access to the :old or the :new values within a trigger. But what if
                  you need access to both? Or is this even possible (I'm fairly new to
                  triggers).
                  >
                  I have to execute a select count(*) from trigger_table where
                  value1=:old.val ue1 and value2=:old.val ue2. Based on this result, log
                  files must be written utilizing the :new values. A simplified example
                  of my trigger is below:
                  >
                  CREATE OR REPLACE TRIGGER mut_trigger
                  AFTER INSERT OR UPDATE OF value1, value2
                  ON table_a
                  REFERENCING OLD AS OLD NEW AS NEW
                  FOR EACH ROW
                  DECLARE
                  numofdocs number;
                  BEGIN
                  select count(*) into numofdocs from table_a where value1=:old.val ue1
                  and value2=:old.val ue2;
                  if (numofdocs 0) then
                  insert into log_table1 values ('Log data', :new.value1,
                  :new.value2);
                  insert into log_table2 values ('Log data', :new.value1,
                  :new.value2);
                  end if;
                  END;
                  >
                  Is there any way to get what I need using one trigger?
                  >
                  Thanks so much,
                  Melissa

                    Comment

                    Previous template Next
                    Working...