Oracle Trigger - Update based on table insert

doot_doot
doot_doot used Ask the Experts™
on
Trying to create an oracle trigger to update table A based on update to table B, which is joined to table C.  Thanks in advance for any assistance:

CREATE OR REPLACE TRIGGER my_trigger
AFTER INSERT OR UPDATE OR DELETE
ON ACMP
 FOR EACH ROW
BEGIN
  UPDATE ACMPE a
SET a.PK =
    (SELECT b.PK
    FROM CAMP b,
    ACMP C
    WHERE
   B.CC = C.CC AND
   A.CI = C.CI);
END;
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2012
Commented:
What is ACMP?  Is that table B?

if so it doesn't make sense with your aliases in the query where CAMP is B and ACMP is C


in any case, don't reference ACMP directly in your query, use the :new  pseudo record



CREATE OR REPLACE TRIGGER my_trigger
    AFTER INSERT OR UPDATE OR DELETE
    ON acmp
    FOR EACH ROW
BEGIN
    UPDATE acmpe a
       SET a.pk =
               (SELECT :new.pk
                  FROM camp c
                 WHERE :new.cc = c.cc AND a.ci = c.ci);
END;

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial