Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2004
    Posts
    76

    Unanswered: Calling SP/UDF from Trigger

    Hi All,

    Is it possible to call storedprocedure/UDF from trigger?

    My DB2 UDB version is 8.1.5.

    Thanks in Advance,
    Jai

  2. #2
    Join Date
    Nov 2005
    Location
    Toronto
    Posts
    65
    http://www-128.ibm.com/developerwork...ein/index.html

    http://publib.boulder.ibm.com/infoce...d/t0011377.htm

    CREATE TABLE t1 ( col1 INTEGER )@
    DB20000I The SQL command completed successfully.

    CREATE TABLE t2 ( col1 INTEGER )@
    DB20000I The SQL command completed successfully.

    CREATE PROCEDURE abc(IN p INTEGER) BEGIN INSERT INTO t1 VALUES(p); END@
    DB20000I The SQL command completed successfully.

    CREATE TRIGGER ins AFTER INSERT ON t2
    REFERENCING NEW AS n
    FOR EACH ROW MODE DB2SQL
    BEGIN ATOMIC
    VALUES ( call_procedure('DB2INST1.ABC', char(new.col1 * 2),
    'SAMPLE', 'DB2INST1', 'db2inst1') );
    END@
    DB20000I The SQL command completed successfully.

    INSERT INTO t2 VALUES ( 20 )@
    DB20000I The SQL command completed successfully.

    -- validate that the trigger has fired - it should update t1
    SELECT * FROM t1@

    COL1
    -----------
    40

    1 record(s) selected.
    IBM Certified DBA for DB2 UDB
    IBM Certified Database Developer for DB2 UDB
    DB2 Tech Support, IBM Toronto Software Lab

  3. #3
    Join Date
    Jul 2004
    Posts
    76
    Is below mentioned link is for 8.1.5 or 8.2?

    http://publib.boulder.ibm.com/infoce...d/t0011377.htm

  4. #4
    Join Date
    Nov 2005
    Location
    Toronto
    Posts
    65
    it's for DB2 V8
    IBM Certified DBA for DB2 UDB
    IBM Certified Database Developer for DB2 UDB
    DB2 Tech Support, IBM Toronto Software Lab

Posting Permissions

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