Results 1 to 4 of 4

Thread: DB2 triggers

  1. #1
    Join Date
    Mar 2006
    Posts
    12

    Unanswered: DB2 triggers

    Hello,

    I have a problem on triggers in DB2. I must create trigger on a table that calls one function, this function inserts on another
    table and returns a value. This value is placed in a field of the table by which the trigger is invoked.

    When executing the create trigger, the following error message is shown: SQLCODE: -797, SQLSTATE: 4298.

    If I eliminate both the "MODIFIES SQL DATA" property in the function and the insert statement, I have no problem creating the trigger.

    Test case:

    CREATE TABLE test1 (
    id INTEGER,
    codigo CHAR(20),
    nombre VARCHAR(80),
    estado INTEGER
    );

    CREATE TABLE test2 (
    id INTEGER,
    user CHAR(20),
    fecha DATE
    );


    CREATE FUNCTION func_test1(
    p_id integer
    )

    RETURNS TABLE(
    field_1 integer
    )

    MODIFIES SQL DATA
    BEGIN ATOMIC

    INSERT INTO test2 VALUES (p_id, CURRENT_USER, CURRENT_DATE);
    RETURN SELECT 0 FROM sysibm.sysdummy1;

    END;

    CREATE TRIGGER test1_upd
    BEFORE UPDATE ON test1
    REFERENCING NEW AS nxt OLD AS prv
    FOR EACH ROW MODE DB2SQL
    BEGIN ATOMIC

    SET nxt.estado = (SELECT field_1 FROM TABLE(func_test1(nxt.id)) as a);

    END;


    This on any agent (Informix, Oracle, Postgres, etc) works without problem.

    Informix:

    CREATE TRIGGER test1_upd
    INSERT ON test1
    REFERENCING NEW AS nxt
    FOR EACH ROW (
    EXECUTE FUNCTION func_test1(nxt.id) INTO estado);

    Postgres:


    CREATE OR REPLACE FUNCTION func_test1_upd() RETURNS TRIGGER AS $$
    DECLARE
    BEGIN

    NEW.estado := func_test1(NEW.id);

    END;
    $$ LANGUAGE 'plpgsql';


    CREATE TRIGGER test1_upd
    AFTER INSERT ON test1
    FOR EACH ROW
    EXECUTE PROCEDURE func_test1_upd();

    Oracle:


    CREATE OR REPLACE TRIGGER test1_upd
    BEFORE INSERT ON test1
    REFERENCING NEW AS nxt
    FOR EACH ROW
    DECLARE
    v_99F20B98_newval number(12) := 0;
    v_99F20B98_incval number(12) := 0;
    BEGIN

    :nxt.estado := func_test1(:nxt.id);

    END test1_upd;




    One more question, how would this trigger be written in DB2?

    Thank you very much.

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I would write the table function as stored procedure. Then this will work right away as well.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Mar 2006
    Posts
    12

    DB2 triggers

    Hello,

    I sorry, but I need use a function since this returned a value. This value is placed in a field of the table that invoke trigger.
    This test case is a summarized example of my problem (the reality is more complex), considering the two problematicas, return a value and of modifying data.

    Tipico example insert in a table and return the serial generated for inform it in a field the another table.

    Thank you very much.

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Stored procedures can return values via OUT parameters and can also return result sets. So this is no argument for using functions.

    As for your example: why not simply select the generated value:
    Code:
    SELECT serial
    FROM   NEW TABLE ( INSERT INTO ... ) AS t
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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