Results 1 to 5 of 5
  1. #1
    Join Date
    May 2013
    Posts
    3

    Unanswered: Begin atomic inside Create Trigger statement in db2 9.1 Linux

    Hi,

    Originally I was trying to create a trigger using a case statement inside it, testing multiple scenarios and signal different errors. It was not working... I was testing, testing until I finally get to the IBM documentation . I copied a example which does not work in my IBM® Data Studio Version 3.2.0.0, which is accessing a DB2 9.1 running on a Linux machine trough JDBC. The query and the error are the following:

    CREATE TRIGGER REORDER
    AFTER UPDATE OF ON_HAND, MAX_STOCKED ON PARTS
    REFERENCING NEW TABLE AS NTABLE
    FOR EACH STATEMENT
    BEGIN ATOMIC
    SELECT ISSUE_SHIP_REQUEST(MAX_STOCKED - ON_HAND, PARTNO)
    FROM NTABLE
    WHERE (ON_HAND < 0.10 * MAX_STOCKED);
    END;

    An unexpected token "END-OF-STATEMENT" was found following "0.10 * MAX_STOCKED)". Expected tokens may include: "<delim_semicolon>".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.14.113

    The error is in the comma after the parentheses in the where clause.

    What can this be? Version issue?

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Firstly, you should probably be using the information center for your DB2 version. Secondly, change the statement terminator in the Data Studio SQL Editor to something other than the semicolon.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    May 2013
    Posts
    3
    Well, changing the the statement terminator really work. What is the reason for that? Data Studio gets lost with the terminators for different things?

    Thank you very much.

  4. #4
    Join Date
    May 2013
    Posts
    3
    Actually, the problem persist. In my actual trigger:

    Code:
    DROP TRIGGER ACCOUNT_V_INS_O$
    
    CREATE TRIGGER ACCOUNT_V_INS_O INSTEAD OF INSERT ON ACCOUNT_V	
       REFERENCING NEW AS NEWORDER
       
       FOR EACH ROW MODE DB2SQL
       BEGIN ATOMIC   
    	   INSERT INTO ORDERS (O_TYPE, O_TRADER, O_AMOUNT, O_STOCK,O_LIMIT)
    		VALUES( CASE WHEN CALL_AMOUNT IS NOT NULL THEN 'CALL' ELSE 'PUT' END,
    	   			NEWORDER.TRADER, 
    	   			CASE WHEN CALL_AMOUNT IS NOT NULL THEN CALL_AMOUNT ELSE PUT_AMOUNT END,
    	   			NEWORDER.STOCK ,
    	   			COALESCE(NEWORDER.CALL_LIMIT, NEWORDER.PUT_LIMIT)
    	   		   ); -- ERROR
    	   			
    	   	CASE WHEN 2 > 1 THEN NULL ELSE 1 END; 
       		
       	END $

    I got:

    An unexpected token "CASE WHEN" was found following " )". Expected tokens may include: "

    ".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.14.113

    As you can see, I changed my statement terminators to $. Am I missing something?

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by pmdusso View Post
    Am I missing something?
    Not missing, I'd say, but superfluous: "CASE WHEN 2 > 1 THEN NULL ELSE 1 END" in the middle of nowhere.
    ---
    "It does not work" is not a valid problem statement.

Tags for this Thread

Posting Permissions

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