1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Simultaneous update in child table

Discussion in 'SQL PL/SQL' started by many_yammy, Jul 3, 2014.

  1. many_yammy

    many_yammy Active Member

    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    80
    Hi all,

    First I have a question.
    Well, assume I have two tables one parent and the other child. First I imported the data into child table and then the parent. Now I am wondering if it is possible to update the foreign key in the child table per each entry to the parent table?(e.g with trigger or ?!)

    Well, my case is a bit complicated. So first I need to know if this idea works or not. And hopefully if the respond is yes I need your help friends :).

    I appreciate your advice in advance.
    Best regards,
    Many
     
  2. many_yammy

    many_yammy Active Member

    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    80
    Ok the whole story is as below:

    I have three tables:
    CITYOBJEC
    Code (SQL):
    CREATE TABLE "ANDREAS"."CITYOBJECT"
       (    "ID" NUMBER NOT NULL ENABLE,
        "CREATION_DATE" DATE,
        "LAST_MODIFICATION_DATE" DATE,
        "UPDATING_PERSON" VARCHAR2(256 BYTE),
        "REASON_FOR_UPDATE" VARCHAR2(4000 BYTE),
         CONSTRAINT "CITYOBJECT_PK" PRIMARY KEY ("ID"));
    CAFM_BUILDING
    Code (SQL):
      CREATE TABLE "ANDREAS"."CAFM_BUILDING"
       (    "ID" NUMBER NOT NULL ENABLE,
        "BUILDING_ID" VARCHAR2(4000 BYTE),
        "FK_CITYOBJECT_ID" NUMBER,
         CONSTRAINT "CAFM_BUILDING_PK" PRIMARY KEY ("ID"),
         CONSTRAINT "CAFM_BUILDING_CITYOBJECT_FK1" FOREIGN KEY ("FK_CITYOBJECT_ID")
          REFERENCES "ANDREAS"."CITYOBJECT" ("ID") ENABLE);
    CAFM_FACADE
    Code (SQL):
    CREATE TABLE "ANDREAS"."CAFM_FACADE"
       (    "ID" NUMBER NOT NULL ENABLE,
        "FACADE_ID" VARCHAR2(4000 BYTE),
        "FK_CAFM_BUILDING" NUMBER NOT NULL ENABLE,
        "FK_CITYOBJECT_ID" NUMBER,
         CONSTRAINT "CAFM_FACADE_PK" PRIMARY KEY ("ID"),
         CONSTRAINT "CAFM_FACADE_CAFM_BUILDING_FK1" FOREIGN KEY ("FK_CAFM_BUILDING")
        REFERENCES "ANDREAS"."CAFM_BUILDING" ("ID") ENABLE,
         CONSTRAINT "CAFM_FACADE_CITYOBJECT_FK1" FOREIGN KEY ("FK_CITYOBJECT_ID")
          REFERENCES "ANDREAS"."CITYOBJECT" ("ID") ENABLE
       );
    Each building has several facades. and In the "CITYOBJECT" table I have per each entry in either of "CAFM_BUILDIN" AND "CAFM_FACADE" 1 entry. It means If I have 2 building one with 3 and the other with 4 facades in general in the "CITYOBJECT" I have 9 rows(2+(3+4)).


    Well. What I have now is a table in it I have "BUILDING_ID", "ID" and all information required for the table "CITYOBJECT". With help of this table I can insert data into the "CITYOBJECT" and the update "FK_CITYOBJECT_ID" in the table "CAFM_BUILDING".

    When the table "CAFM_BUILDING" was updated, the column "FK_CIYTOBJECT_ID" in the table "CAFM_FACADE" will be updated. This has been done in the way that for all facades belong to a building, the "FK_CIYTOBJECT_ID" of the facades will be the same as its building's "FK_CIYTOBJECT_ID".

    So now the tables I have in this level can be populated with this insert queries:

    [
    Code (SQL):
    INSERT INTO CAFM_BUILDING (ID, BUILDING_ID, FK_CITYOBJECT_ID)
    VALUES (1, 100.08, 1);
    INSERT INTO CAFM_BUILDING (ID, BUILDING_ID, FK_CITYOBJECT_ID)
    VALUES (2, 100.10, 2);

    INSERT INTO CITYOBJECT (ID, UPDATING_PERSON )
    VALUES (1, 'Many');
    INSERT INTO CITYOBJECT (ID, UPDATING_PERSON )
    VALUES (2, 'Many');

    INSERT INTO CAFM_FACADE (ID, FACADE_ID, FK_CAFM_BUILDING)
    VALUES (1, '100.08_nord', 1);
    INSERT INTO CAFM_FACADE (ID, FACADE_ID, FK_CAFM_BUILDING)
    VALUES (2, '100.08_west', 1);
    INSERT INTO CAFM_FACADE (ID, FACADE_ID, FK_CAFM_BUILDING)
    VALUES (3, '100.08_sued', 1);
    INSERT INTO CAFM_FACADE (ID, FACADE_ID, FK_CAFM_BUILDING)
    VALUES (4, '100.08_ost', 1);
    INSERT INTO CAFM_FACADE (ID, FACADE_ID, FK_CAFM_BUILDING)
    VALUES (5, '100.10_west', 2);
    INSERT INTO CAFM_FACADE (ID, FACADE_ID, FK_CAFM_BUILDING)
    VALUES (6, '100.10_sued', 2);
    INSERT INTO CAFM_FACADE (ID, FACADE_ID, FK_CAFM_BUILDING)
    VALUES (7, '100.10_ost', 2);
    Now what I am thinking of is:
    For each entry in the "CAFM_FACADE" take the corresponding row from "CITYOBJECT" (where its "ID" is the same as "FK_CITYBJECT_ID" in the "CAFM_FACADE"), Insert it to the "CITYOBJECT" with a new "ID" and after inserting this row update the corresponding row in the "CAFM_FACADE".

    I think there should be a possibility like a for loop or something as it is possible in Java for example and I need to do such a thing with PL/SQL.

    Looking forward to see your comments:).

    Best regards,
    Mandana
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    With a properly written trigger it is possible:


    Code (SQL):

    SQL> CREATE OR REPLACE TRIGGER updt_fk_vals
      2  BEFORE UPDATE ON pktest
      3  FOR each ROW
      4  BEGIN
      5          UPDATE fktest
      6          SET yumpa=:NEW.yumpa
      7          WHERE yumpa=:OLD.yumpa;
      8  END;
      9  /


    TRIGGER created.


    SQL>
    SQL> SHOW errors

    No errors.

    SQL>
    SQL> UPDATE pktest
      2  SET yumpa=yumpa+100000;


    100100 ROWS updated.


    SQL>
     

    Notice how that trigger is written -- it uses both the before update and the updated value for the primary key. This is not a recommended practice as primary keys are intended to be set once and not changed.
     
  4. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Mandana,

    Yes it's possible with a Cursor/For Loop in PL/SQL. Try & adapt the following:

    Code (SQL):
    -- Create a sequence first starting with 3 (for example, based on your sample data)
    CREATE SEQUENCE s_cityobject_id START WITH 3;
    -- Then run following script:
    DECLARE
      l_id cityobject.id%TYPE;
      CURSOR c_facade
      IS
        SELECT ID,
          facade_id,
          fk_cityobject_id
        FROM cafm_facade FOR UPDATE OF fk_cityobject_id;
    BEGIN
      FOR c IN c_facade
      LOOP
        INSERT INTO cityobject (ID, updating_person)
          VALUES (s_cityobject_id.NEXTVAL, 'Many')
        RETURNING ID INTO l_id;
        UPDATE cafm_facade
          SET fk_cityobject_id = l_id WHERE CURRENT OF c_facade;
      END LOOP;
    END;
    /