Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2004
    Posts
    12

    Question Unanswered: Select Into From A Trigger

    Hi All,

    I hope that they can help me.

    I need to use a SELECT INTO statement, because i want get a value from table in a trigger.

    This is my code:

    ----------------------------------------------------
    CREATE TRIGGER DB2ADMIN.FAC_PROGR_INS
    AFTER INSERT ON DB2ADMIN.XTFACOM1F
    REFERENCING NEW AS INSERTADO
    FOR EACH ROW MODE DB2SQL
    BEGIN ATOMIC
    DECLARE v_NombProvee CHAR(80);
    DECLARE v_NombPrPg CHAR(80);

    SELECT nombcomp INTO v_NombProvee
    FROM DB2ADMIN.MPERSO1F
    WHERE compania = INSERTADO.compania
    AND persona = INSERTADO.proveedor;

    SELECT nombcomp INTO v_NombPrPg
    FROM DB2ADMIN.MPERSO1F
    WHERE compania = INSERTADO.compania
    AND persona = INSERTADO.provpago;
    END

    INSERT INTO TEMPTABLE(C1,C2)
    VALUES(v_NombProvee,v_NombPrPg );

    END
    ----------------------------------------------------


    ...and I get the message error:

    --------------------------------------------------------------------------
    [IBM][CLI Driver][DB2/NT] SQL0104N Se ha encontrado un símbolo imprevisto "v_NombProvee"
    a continuación de "SELECT nombcomp INTO". Entre los símbolos esperados se puede incluir: "<space>".
    LINE NUMBER=1. SQLSTATE=42601
    -------------------------------------------------------------------------


    Whats is the problem?

    Thanks Advisors.

    Wil.

  2. #2
    Join Date
    Sep 2004
    Posts
    12

    I Correct the code

    Please, ignore the first END, forgets to clear it when reducing I code. The message is the same.

    Thanks,

    Wil

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    SELECT .. INTO is not a dynamic SQL statement and therefore cannot be used in a trigger. You could try using
    Code:
    SET (v_NombProvee) = (SELECT nombcomp FROM DB2ADMIN.MPERSO1F WHERE compania = INSERTADO.compania AND persona = INSERTADO.proveedor);
    instead. Better yet, use
    Code:
    insert into ... select ...

  4. #4
    Join Date
    May 2004
    Posts
    11
    I'm not sure, but I think "SELECT INTO" cannot be used in triggers... (?)

    How about if you write the select's as following ?

    SET v_NombProvee = (SELECT nombcomp
    FROM DB2ADMIN.MPERSO1F
    WHERE compania = INSERTADO.compania
    AND persona = INSERTADO.proveedor);

    SET v_NombPrPg = (SELECT nombcomp
    FROM DB2ADMIN.MPERSO1F
    WHERE compania = INSERTADO.compania
    AND persona = INSERTADO.provpago);


    Antti

  5. #5
    Join Date
    Sep 2004
    Posts
    12

    Ok

    Must be that the triggers not support the SELECT INTO in this version.

    Well, now i would to use the other variant.

    Thanks for your help.

    Wil

Posting Permissions

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