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:
set b = :new.b
where a = :new.a;
(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?
Glen A. S.
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.
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.
Click Here to Expand Forum to Full Width