Results 1 to 5 of 5

Thread: Trigger error

  1. #1
    Join Date
    Aug 2007
    Posts
    56

    Unanswered: Trigger error

    CREATE TRIGGER SERDB.TRE07ADD
    AFTER INSERT ON SERDB.E07_PDD_SVS_RATE_T
    REFERENCING NEW AS NEWV
    FOR EACH ROW MODE DB2SQL
    CALL SERDB.E06ADD(NEWV.E07_RATE_CHG_ID,
    NEWV.E07_PDD_SVS_ID,
    NEWV.E07_PDD_SVS_RATE,
    NEWV.E07_RATE_EFF_DT,
    NEWV.E07_WEB_USER_ID);
    COMMIT;

    The above trigger was created without error, however, when I attempt to insert a new record on the table I receive the following error:

    Error: COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2] SQL0723N An error occurred in a triggered SQL statement in trigger "SERDB.TRE07ADD". Information returned for the error includes SQLCODE "-751", SQLSTATE "38003" and message tokens "STORED PROCEDURE,SERDB.E06ADD ". SQLSTATE=09000, SQL State: 09000, Error Code: -723

    The stored procedure the trigger calls was also created without error. The parameters match up, I don't understand why I am getting this error. I've checked the errors in the reference manual but the text doesn't help me either.

    Does anyone have an idea?

    I am running DB2 V7.1 on Z/os. If you need to look at the stored procedure, I can post it as well. Thanks in advance.

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    When you call a stored procedure in a trigger, the procedure must adhere to the restrictions on SQL statements allowed in a trigger. This is not the case here. DB2 attempts to execute some statement in the procedure, which cannot run in the trigger context (example: COMMIT/ROLLBACK). Hence, this error is raised.
    $ db2 "? sql751"


    SQL0751N Routine "<routine-name>" (specific name "<specific-name>")
    attempted to execute a statement that is not allowed.

    Explanation:

    The program used to implement the body of a routine is not allowed to
    issue any connection statements. If the routine is a function or method,
    COMMIT and ROLLBACK (without the SAVEPOINT option) are also not allowed.
    If the routine is a procedure and is called within a trigger, function,
    method, or dynamic compound statement, a COMMIT or ROLLBACK statement is
    not allowed in the procedure.

    User response:

    Remove the statement that is not allowed, then recompile the program.

    sqlcode: -751
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Aug 2007
    Posts
    56

    Trigger problem continued

    The stored procedure below builds without error using stored procedure builder. The system DBA thinks there is an offending statement that causes the trigger to abend. I have checked the validity of statements (http://publib.boulder.ibm.com/infoce...g/bjnqspsh.htm) and based on what the document says, my statements are in order. Am I missing something here?


    CREATE PROCEDURE SERDB.E06ADD (IN NRATE_CHG_ID INT,
    IN NPDD_SVS_ID SMALLINT,
    IN NPDD_SVS_RATE DECIMAL(6,2),
    IN NRATE_EFF_DT DATE,
    IN NWEB_USER_ID CHAR(13))

    LANGUAGE SQL
    COLLID SER
    WLM ENVIRONMENT DDSNSPENV
    MODIFIES SQL DATA
    RUN OPTIONS 'NOTEST(ALL,*,,VADTCPIP&10.81.128.88:*)'
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    P1: BEGIN

    --Declare variables

    DECLARE SERVICESID_0 INT;
    DECLARE PDDSVSID_0 SMALLINT;
    DECLARE RATECHGID_0 INT;
    DECLARE CONSUMERID_0 INT;
    DECLARE BUDGETID_0 INT;
    DECLARE FUNDINGTYPE_0 CHAR(1);
    DECLARE RATEBEGDT_0 DATE;
    DECLARE RATEENDDT_0 DATE;
    DECLARE RATEENDDT_1 DATE;
    DECLARE BUDGETEDUNITS_0 SMALLINT;
    DECLARE BUDGETEDUNITS_1 SMALLINT;
    DECLARE OLDBDGTUNITS SMALLINT;
    DECLARE NEWBDGTUNITS SMALLINT;
    DECLARE MTHLYUNITS_0 SMALLINT;
    DECLARE DAYS_BETWEEN SMALLINT;
    DECLARE DAYS_BETWEEN1 SMALLINT;
    DECLARE MONTHS_BETWEEN SMALLINT;
    DECLARE BUDGET_MONTHS SMALLINT;
    DECLARE DAYS_PER_MNTH SMALLINT;
    DECLARE AMOUNT DEC(6,2);
    DECLARE TOTAL_AMOUNT DEC(6,2);
    DECLARE BUDGET_AMOUNT DEC(7,2);
    DECLARE SERVICECOST1 DEC(6,2);
    DECLARE SERVICECOST2 DEC(6,2);
    DECLARE SVSRATE1 DEC(6,2);
    DECLARE END_TABLE INT DEFAULT 0;

    DECLARE C1 CURSOR FOR
    SELECT E06_SERVICES_ID,
    E06_PDD_SVS_ID,
    E06_RATE_CHG_ID,
    E06_CONSUMER_ID,
    E06_BUDGET_ID,
    E06_FUNDING_TYPE,
    E06_RATE_BEG_DT,
    E06_RATE_END_DT,
    E06_BUDGETED_UNITS
    FROM SERDB.E06_SERVICES_T
    WHERE E06_PDD_SVS_ID = NPDD_SVS_ID
    AND ((NRATE_EFF_DT BETWEEN E06_RATE_BEG_DT AND E06_RATE_END_DT)
    OR (E06_RATE_BEG_DT >= NRATE_EFF_DT))
    ORDER BY E06_SERVICES_ID;

    DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET END_TABLE = 1;

    -- DECLARE HANDLER - ROLLBACK WILL KILL UPDATE IF SQLSTATE ERROR
    DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;

    OPEN C1;
    FETCH C1 INTO SERVICESID_0,
    PDDSVSID_0,
    RATECHGID_0,
    CONSUMERID_0,
    BUDGETID_0,
    FUNDINGTYPE_0,
    RATEBEGDT_0,
    RATEENDDT_0,
    BUDGETEDUNITS_0;

    WHILE END_TABLE = 0 DO
    SELECT SUM(E06_BUDGETED_UNITS * E07_PDD_SVS_RATE) INTO AMOUNT
    FROM SERDB.E06_SERVICES_T
    INNER JOIN SERDB.E07_PDD_SVS_RATE_T
    ON E07_RATE_CHG_ID = E06_RATE_CHG_ID
    INNER JOIN SERDB.E05_CONS_BUDGET_T
    ON E05_CONSUMER_ID = E06_CONSUMER_ID
    AND E05_BUDGET_ID = E06_BUDGET_ID
    WHERE E06_BUDGET_ID = BUDGETID_0
    AND E06_SERVICES_ID <> SERVICESID_0
    GROUP BY E06_BUDGET_ID;

    SELECT E07_PDD_SVS_RATE
    INTO SVSRATE1
    FROM SERDB.E07_PDD_SVS_RATE_T A,
    SERDB.E06_SERVICES_T B
    WHERE PDDSVSID_0 = A.E07_PDD_SVS_ID
    AND RATECHGID_0 = A.E07_RATE_CHG_ID;

    SELECT MAX(E10_BUDGET_AMT)
    INTO BUDGET_AMOUNT
    FROM SERDB.E10_BUDGET_MSTR_T,
    SERDB.E05_CONS_BUDGET_T
    WHERE ((E10_BUDGET_END_DT IS NULL AND
    (E10_BUDGET_BEG_DT BETWEEN E05_BUDGET_BEG_DT AND E05_BUDGET_END_DT
    OR E10_BUDGET_BEG_DT < E05_BUDGET_BEG_DT))
    OR (E10_BUDGET_END_DT IS NOT NULL AND
    ((E10_BUDGET_BEG_DT BETWEEN E05_BUDGET_BEG_DT AND E05_BUDGET_END_DT
    AND E10_BUDGET_END_DT > E05_BUDGET_END_DT)
    OR (E10_BUDGET_BEG_DT < E05_BUDGET_BEG_DT AND
    E10_BUDGET_END_DT > E05_BUDGET_END_DT))))
    AND E05_BUDGET_ID = BUDGETID_0;

    IF NPDD_SVS_ID NOT IN (5, 6)
    AND RATEBEGDT_0 >= NRATE_EFF_DT
    THEN
    UPDATE SERDB.E06_SERVICES_T
    SET E06_RATE_CHG_ID = NRATE_CHG_ID
    WHERE E06_SERVICES_ID = SERVICESID_0;

    ELSE IF NPDD_SVS_ID NOT IN (5, 6)
    THEN
    --Nbr of Days
    SELECT DAYS(RATEENDDT_0)-DAYS(RATEBEGDT_0)
    INTO DAYS_BETWEEN
    FROM SYSIBM.SYSDUMMY1;

    SET DAYS_PER_MNTH = 30;

    SELECT INT(ROUND(DEC(DAYS_BETWEEN/(DEC(DAYS_PER_MNTH,5,2)),5,2),0))
    INTO BUDGET_MONTHS
    FROM SYSIBM.SYSDUMMY1;

    IF BUDGET_MONTHS <> 0
    THEN
    SELECT INT(ROUND(DEC(BUDGETEDUNITS_0/(DEC(BUDGET_MONTHS,5,2)),5,2),0))
    INTO MTHLYUNITS_0
    FROM SYSIBM.SYSDUMMY1;

    --Nbr of days between
    SELECT DAYS(NRATE_EFF_DT)-DAYS(RATEBEGDT_0)
    INTO DAYS_BETWEEN1
    FROM SYSIBM.SYSDUMMY1;

    SELECT INT(ROUND(DEC(DAYS_BETWEEN1/(DEC(DAYS_PER_MNTH,5,2)),5,2),0))
    INTO MONTHS_BETWEEN
    FROM SYSIBM.SYSDUMMY1;

    IF MONTHS_BETWEEN < 1
    THEN
    SET MONTHS_BETWEEN = 1;

    SET OLDBDGTUNITS = MTHLYUNITS_0 * MONTHS_BETWEEN;
    SET NEWBDGTUNITS = BUDGETEDUNITS_0 - OLDBDGTUNITS;
    SET SERVICECOST1 = (NEWBDGTUNITS * NPDD_SVS_RATE);
    SET SERVICECOST2 = (OLDBDGTUNITS * SVSRATE1);
    SET TOTAL_AMOUNT = (SERVICECOST1 + SERVICECOST2 + AMOUNT);

    IF TOTAL_AMOUNT <= BUDGET_AMOUNT
    THEN
    SET RATEENDDT_1 = NRATE_EFF_DT - 1 DAY;
    UPDATE SERDB.E06_SERVICES_T
    SET E06_BUDGETED_UNITS = OLDBDGTUNITS,
    E06_RATE_END_DT = RATEENDDT_1
    WHERE E06_SERVICES_ID = SERVICESID_0;
    ELSE
    UPDATE SERDB.E05_CONS_BUDGET_T
    SET E05_BDGT_LCK_IND = 'Y'
    WHERE E05_BUDGET_ID = BUDGETID_0;

    INSERT INTO SERDB.E06_SERVICES_T
    VALUES(DEFAULT,
    PDDSVSID_0,
    NRATE_CHG_ID,
    CONSUMERID_0,
    BUDGETID_0,
    FUNDINGTYPE_0,
    NRATE_EFF_DT,
    RATEENDDT_0,
    NEWBDGTUNITS,
    NWEB_USER_ID);

    IF NPDD_SVS_ID IN (5, 6)
    AND NRATE_EFF_DT <= RATEBEGDT_0 + 32 DAYS
    THEN
    SET BUDGETEDUNITS_1 = BUDGETEDUNITS_0;
    SET BUDGETEDUNITS_0 = 0;
    SET RATEENDDT_1 = NRATE_EFF_DT - 1 DAY;

    UPDATE SERDB.E06_SERVICES_T
    SET E06_BUDGETED_UNITS = BUDGETEDUNITS_0,
    E06_RATE_END_DT = RATEENDDT_1
    WHERE E06_SERVICES_ID = SERVICESID_0;

    INSERT INTO SERDB.E06_SERVICES_T
    VALUES(DEFAULT,
    PDDSVSID_0,
    NRATE_CHG_ID,
    CONSUMERID_0,
    BUDGETID_0,
    FUNDINGTYPE_0,
    NRATE_EFF_DT,
    RATEENDDT_0,
    BUDGETEDUNITS_1,
    NWEB_USER_ID);

    END IF;
    END IF;
    END IF;
    END IF;
    END IF;
    END IF;


    FETCH C1 INTO SERVICESID_0,
    PDDSVSID_0,
    RATECHGID_0,
    CONSUMERID_0,
    BUDGETID_0,
    FUNDINGTYPE_0,
    RATEBEGDT_0,
    RATEENDDT_0,
    BUDGETEDUNITS_0;
    END WHILE;
    CLOSE C1;

    END P1

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by citi
    Am I missing something here?
    Quote Originally Posted by citi
    DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
    .............................
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Aug 2007
    Posts
    56
    Thank you!!

Posting Permissions

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