Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2009
    Posts
    2

    Unanswered: Create Triggers By Join Of Two Tables

    We have created an update trigger for a join of two tables which is given below,
    CREATE TRIGGER X
    AFTER INSERT ON A
    REFERENCING NEW AS N
    FOR EACH ROW MODE DB2SQL
    BEGIN ATOMIC
    DECLARE TESTVAR1 CHAR(1);
    DECLARE TESTVAR2 CHAR(9);
    SET (TESTVAR1,TESTVAR2) = (SELECT TEST_VAR1 TESTVAR1,
    SELECT TEST_VAR2 TESTVAR2
    FROM B
    WHERE B.XXX = N.XXX
    AND B.YYY = 'C')
    INSERT INTO C
    VALUES (N.XXX,N.YYY,
    :TESTVAR1,:TESTVAR2,N.ZZZ) ;
    END

    When executing the above trigger,it is failing with sql code -919.Can anybody please help me out in solving the error?

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Before creating trigger, you made some basic syntax errors in SELECT and INSERT statement.

    For example:
    1)
    SELECT TEST_VAR1 TESTVAR1, SELECT TEST_VAR2 TESTVAR2 FROM B ...

    2)
    SET (...) = (...) INSERT ...

    3)
    VALUES (N.XXX,N.YYY,:TESTVAR1,:TESTVAR2,...


    And, what are your platform, DB2 version/release, full error message?

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Also, why the roundabout way to say
    "insert into table
    select n.xxx, n.yyy, b.TESTVAR1, b.TEST_VAR2
    from table2
    where..."

    Dave

Posting Permissions

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