Geek asks and answers

Before starting a trigger with a stored procedure call (DB2 LUW 9.5)

I am trying to create a trigger BEFORE INSERTthat checks the input value of a field and replaces it with the same field on another line if that field is null. However, when I add a statement CALLto my trigger, the error " The trigger "ORGSTRUCT.CSTCNTR_IN" is defined with an unsupported triggered SQL statement" is returned . I checked the documentation and saw that cursors are not supported in BEFORE (part of the reason for creating a stored procedure in the first place), but even when I delete the cursor declaration from the stored procedure, the call still generates the same error.

Trigger:

CREATE TRIGGER orgstruct.cstcntr_IN
        NO CASCADE
        BEFORE INSERT ON orgstruct.tOrgs
        REFERENCING NEW AS r
        FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
    DECLARE prnt_temp BIGINT;
    DECLARE cstcntr_temp CHAR(11);

    SET prnt_temp = r.prnt;
    SET cstcntr_temp = r.cstcntr;

    CALL orgstruct.trspGetPrntCstCntr(prnt_temp,cstcntr_temp);
    SET r.cstcntr = cstcntr_temp;
END

Stored Procedure:

CREATE PROCEDURE orgstruct.trspGetPrntCstCntr (
    IN  p_prnt              BIGINT,
    OUT p_cstcntr       CHAR(11)
)
SPECIFIC trGetPrntCstCntr
BEGIN
    IF p_prnt IS NULL THEN
        RETURN;
    END IF;

    BEGIN
        DECLARE c1 CURSOR
            FOR
                SELECT cstcntr
                FROM orgstruct.tOrgs
                WHERE id = p_prnt
            FOR READ ONLY;
        OPEN c1;
        FETCH FROM c1 INTO p_cstcntr;
        CLOSE c1;
    END;
END

According to the documentation, CALLresolved in a trigger BEFORE, so I don’t understand what the problem is.

+3
source share
1

A , , proc , .

SQL MODIFIES SQL DATA, . , READS SQL DATA; .

: - ; , :

create trigger orgstruct.cstcntr_IN
   no cascade
   before insert on orgstruct.tOrgs
   referencing new as r
   for each row
   mode db2sql
   set r.cstcntr = case 
                     when r.p_prnt is not null 
                       then (select cstcntr from tOrgs where id = r.p_prnt fetch first 1 row only) 
                     else r.cstcntr 
                   end;

, , . , .

FYI: , , , CSTCNTR NULL. , , .: -)

+2

More articles:


All Articles