3 Replies Latest reply: Feb 26, 2012 10:45 PM by Toon Koppelaars RSS

    Trigger

    899280
      Hi,
      I have a table as below


      with table1 as (
      select '1' id,'' user_id, 'AAA' name, '123' identifier,'K' set_value from dual union all
      select '2' id,'' user_id, 'BBB' name, '321' identifier,'K' set_value from dual union all
      select '3' id,'' user_id, 'CCC' name, '123' identifier,'K' set_value from dual union all
      select '4' id, '' user_id, 'CCC' name, '456' identifier,'K' set_value from dual )
      select * from table1

      If someone update the user_id column to 'SUPER_USER' as below the
      update table1
      set user_id = 'SUPER_USER'
      where id = 1

      A trigger should take the identifier value of that particular id and update all the user_id to 'SUPER_USER' and set_value to 'U' for that corresponding identifier value.here the identifier value is '123'.so the trigger updated the user_id to 'SUPER_USER' and set_value to 'U' for all the identifier with 123 as below (output should be as below)


      with table1 as (
      select '1' id,'SUPER_USER' user_id, 'AAA' name, '123' identifier,'U' set_value from dual union all
      select '2' id,'' user_id, 'BBB' name, '321' identifier,'K' set_value from dual union all
      select '3' id,'SUPER_USER' user_id, 'CCC' name, '123' identifier,'U' set_value from dual union all
      select '4' id, '' user_id, 'CCC' name, '456' identifier,'K' set_value from dual )
      select * from table1


      please let help to write a trigger for above situation
        • 1. Re: Trigger
          523861
          why don't you just update the correct rows the first time around?
          • 2. Re: Trigger
            rp0428
            >
            A trigger should take the identifier value of that particular id and update all
            >
            Well that isn't going to work. You have basically stated the problem as
            >
            When any one row is updated a special way the trigger fired by the update should update all other rows in the table that have certain values.
            >
            A trigger is fired as part of a transaction and in some cases may be fired multiple times for the same event. A row level trigger has no way of knowing what other rows were updated before it fired for this new row or what other rows might be updated after if fires for this new row.

            So even if the trigger fires for row 2 and tries to update row 3 it could find that row 3 had already been updated by this transaction and would want to update it again. Look up MUTATING TABLE on the net to see why you can't do this.

            Also if the trigger fired for row 2 and was able to update another row (perhaps row 1) the same transaction could later update row 1 and wipe out the change that the row 2 trigger had just made.

            That type of procedural model is dysfunctional. A trigger can't be used this way.
            • 3. Re: Trigger
              Toon Koppelaars
              What is the meaning of that table and it's columns?
              It looks like this table is not in third normal form...

              If you can't change the table-structure, then can you instead of having your application-code issue an update statement against this table, have your application-code execute a stored procedure? This stored procedure would have two IN parameters: ID and USER_ID. And inside the stored procedure you would code out how to update the multiple rows.

              Edited by: Toon Koppelaars on Feb 27, 2012 5:40 AM

              Or maybe you can just change the update-statement that your application-code issues into this:
              update table1 T1
              set T1.user_id = 'SUPER_USER', T1.set_value = 'U'
              where T1.identifier in
                (select T2.identifier
                 from table1 T2
                 where T2.id = 1);
              Still much better than trying to develop triggers to do what you stated...