Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2014
    Posts
    8

    Unanswered: trigger variable

    [CREATE OR REPLACE TRIGGER TEST.TEST_ID_TRIGGER
    BEFORE INSERT ON TEST.CHRY_TEST_CASES FOR EACH ROW
    BEGIN ATOMIC
    DECLARE
    v_var1 VARCHAR(5);
    v_var2 VARCHAR(5);
    SET v_var1 = SELECT TEST_DOMAIN FROM TEST.CHRYSALIDE_TEST_CASES;
    v_var2 = SELECT TEST_DOMAIN FROM TEST.CHRY_TEST_CASES;
    set test_id = v_var1||'_'||v_var2||'_'||select lpad(max(substr(test_id,11,3))+1,3,0) from
    test.chry_test_cases where TEST_DOMAIN = v_var1 and TABLE_NAME = v_var2
    END;]

    Could someone help out? Can variables be used in this case (trigger?)

  2. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    Can variables be used in a trigger? Yes
    Is your code correct? No

    SET v_var1 = SELECT TEST_DOMAIN FROM TEST.CHRYSALIDE_TEST_CASES
    That SELECT returns several rows so you cannot assign its result to a variable. Try:
    SET v_var1 = NEW.TEST_DOMAIN

    set test_id = ...
    This variable is not declared. If this is a column you may try:
    SET NEW.TEST_ID = ...

    You should read about transition variables.
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

Posting Permissions

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