Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2006
    Posts
    4

    Unanswered: help~the transaction pluzzle me

    BEGIN
    SELECT BOARD_ROOT_ID,BOARD_DEPTH+1,BOARD_PATH,BOARD_ID INTO v_rootId,v_depth,v_path,v_id FROM TB_BOARD WHERE BOARD_ID = v_pid;
    IF v_path = '0' THEN
    SET v_path = CHAR(v_id);
    ELSE
    SET v_path =CONCAT(CONCAT( v_path , ','),CHAR(v_id));
    END IF;
    SELECT COALESCE(MIN(BOARD_ID),(SELECT BOARD_ORDER_ID+1 FROM TB_BOARD WHERE BOARD_ID = v_pid)) INTO v_orderId FROM TB_BOARD WHERE BOARD_PARENT_ID = v_pid;
    --决定了新加的栏目在前面
    --如果新的栏目在后面,有点复杂,try next two sqls.
    --SELECT COALESCE(MAX(BOARD_ID),(SELECT ORDERID FROM TB_BOARD WHERE BOARD_ID = v_pid)) INTO v_orderId FROM TB_BOARD WHERE BORAD_PARENT_ID = v_pid;
    --SELECT COALESCE(MAX(BOARD_ID),v_orderId) INTO v_orderId FROM TB_BOARD WHERE BOARD_ORDER_ID = v_orderId AND BOARD_ROOT_ID = v_rootId;
    UPDATE TB_BOARD SET BOARD_ORDER_ID = BOARD_ORDER_ID + 1 WHERE BOARD_ROOT_ID = v_rootId AND BOARD_ORDER_ID >= v_orderId;
    INSERT INTO TB_BOARD(BOARD_ID,BOARD_NAME,BOARD_DESC,BOARD_LINK ,BOARD_PARENT_ID,BOARD_PATH,BOARD_DEPTH,BOARD_ROOT _ID,BOARD_ORDER_ID,BOARD_CHILD_COUNT,BOARD_ISCLOSE D,BOARD_ISTOPIC,BOARD_ISCHKTOPIC,BOARD_ISCOMMENT,B OARD_ISCHKCOMMENT)
    VALUES (DEFAULT,v_name,v_desc,v_link,v_pid,v_path,v_depth ,v_rootId,v_orderId,0,v_close,v_tp,v_chktp,v_c,v_c hkc);
    UPDATE TB_BOARD SET BOARD_CHILD_COUNT = BOARD_CHILD_COUNT + 1 WHERE BOARD_ID = v_pid;
    END;
    these sqls in a store procedure,
    i want to know whether they are implemented in a transaction?
    the symbol ';' is a commit command?
    how to do?

    thanks a lot.
    Last edited by floatingwaterboy; 03-18-06 at 06:15.

  2. #2
    Join Date
    Dec 2005
    Posts
    39
    Autocommit is turned on by default in udb in which case each statement will be considered as a transaction. If not turned on you need to explicitly commit or rollback as needed. ";" is not commit command but statement delimiter.

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by murali_sb
    Autocommit is turned on by default in udb in which case each statement will be considered as a transaction. If not turned on you need to explicitly commit or rollback as needed. ";" is not commit command but statement delimiter.
    Auto-commit after each statement is not in effect for SQL stored procedures by default.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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