Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2002
    Posts
    227

    Unanswered: triggers to update one column on 3 tables

    hi,
    I've three tables:
    TABLE TN:
    COD_ID.........VARCHAR2(32) primary key
    NAME...........VARCHAR2(64) NOT NULL

    COD_ID..............NAME
    A12.................AT32
    A13.................AT33
    A14.................AT35
    A30.................AT32

    TABLE TM:
    ID_X..........VARCHAR2(32) PRIMARY KEY
    NAME..........VARCHAR2(64)

    ID_X..............NAME
    A12
    A13
    A14
    A30

    TABLE TL:
    COD_ID..........VARCHAR2(32) FOREIGN KEY ON TN
    ID_X............VARCHAR2(32) FOREIGN KEY ON TM
    TL_ID...........VARCHAR2(32) PRIMARY KEY
    NAME............VARCHAR2(64)

    I'd like to create triggers (or procedure) that update automatically the value NAME in the tables TM and TL.

    for example:
    If I insert into TN a new COD_ID (A39) and a new NAME (AT38), when I insert a new ID_X in tble TM, I'd like
    to have the value AT38 in NAME of TM, and when I insert a new record in table TL I'd like
    to have the value AT38 in NAME of TL.
    Also when I update NAME of TN.

    I tried with these triggers:

    CREATE OR REPLACE TRIGGER TN_PR_BEFORE_UPD
    BEFORE UPDATE OF NAME ON TL
    FOR EACH ROW
    DECLARE
    tmpVar NUMBER;
    BEGIN

    update TL set NAME=:new.NAME where TL.COD_id=:new.COD_id;

    UPDATE TM SET NAME=:NEW.NAME WHERE ID_X=:NEW.COD_ID;

    END TN_PR_BEFORE_UPD;


    CREATE OR REPLACE TRIGGER TN_COD_ID
    BEFORE UPDATE OF COD_ID ON TL
    FOR EACH ROW
    BEGIN
    SELECT NAME INTO :NEW.NAME FROM TN WHERE TN.COD_ID=:NEW.COD_ID;
    END TN_COD_ID;

    CREATE OR REPLACE TRIGGER TL_pr
    BEFORE UPDATE OF NAME ON TL
    BEGIN
    UPDATE TM SET NAME=:NEW.NAME WHERE ID_X=:NEW.ID_X AND :NEW.ID_X IS NOT NULL;
    END TL_pr;

    CREATE OR REPLACE TRIGGER TL_PR_BEFORE_INSERT
    before INSERT ON TL
    FOR EACH ROW
    DECLARE
    tmpVar varchar2(64);
    BEGIN

    SELECT NAME
    into tmpVar
    FROM TN
    WHERE COD_ID=:NEW.COD_ID;

    END TL_PR_BEFORE_INSERT;


    when I update TN triggers run CORRECTLY but when I insert a new record in TM, TN, TL I don't see a new NAME.


    What I wrong??

    Thanks
    Raf

  2. #2
    Join Date
    Sep 2003
    Location
    Virginia, USA
    Posts
    246
    You only need the trigger on the first table (where the user is inserting data.) Your other triggers are overwriting the :NEW data with the existing NULL data. Just delete the other triggers and you should be fine.
    MarkRem
    Author, Oracle Database 10g: From Nuts to Soup
    http://www.remidata.com/book_nuts2soup.htm

Posting Permissions

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