DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: trigger newbie needs help

Hybrid View

  1. #1
    Join Date
    Aug 2000
    Location
    Alberta
    Posts
    82

    trigger newbie needs help

    I have been asked to have one table, we'll call it "1", have column B in table "2" get updated with the same value as it's own Column "B" where column "A" in both tables has the same value. This was whether or not a row was inserted or updated.

    That seemed easy enough, even for a trigger newbie like me, for Table 1 I created this trigger:

    begin
    update 2
    set b = :new.b
    where a = :new.a;
    end;

    (this trigger fires before insert or update of column B for each row)

    I put that in place, then it became apparent that although table 2 is a quasi-child of table 1, rows can be inserted in to table 2 independantly, and in this case they want a trigger to go check table 1 column A and see if it has the same value for column A in the newly inserted row, and if so, update column B with the same value as column B in table 1.

    I'm thinking I have to populate a variable with the value of A in the newly inserted row in table 2, and somehow retrieve the value of B in Table 1 (where 1.A = 2.A) to update the new B in table 2.

    Suggestions on how best to approach this?

    TIA
    Glen A. S.

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    You can use autonomous transaction to achieve this.

    But when parent and child relationship exists, I would not do what you are trying to do. Basically, you have to design a system that works in all conditions. You may have to go back your drawing board.

    Tamil

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    You are setting yourself up for deadlock city, baby. I would put a FK on the quasi child table so they MUST insert into 1 before they insert into 2.
    Jeff Hunter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width