Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Location
    Jaipur, India
    Posts
    40

    Red face Unanswered: Trigger creation problem

    Hi,

    I am trying to create a After Insert Trigger on a table.
    In the trigger I required to call a stored procedure but its giving some error.

    I am attaching the screenshots for your ready reference.

    Please provide me some pointers regarding this.

    Thanking you in anticipation.

    Amit Kanodia
    Attached Files Attached Files

  2. #2
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    i dont think ..its the right ways to call procedure from Triggers

    example:

    CREATE TRIGGER trig1 AFTER UPDATE ON t1
    REFERENCING NEW AS n
    FOR EACH ROW MODE DB2SQL
    WHEN (n.c1 > 100);
    BEGIN ATOMIC
    DECLARE rs INTEGER DEFAULT 0;
    CALL proc(n.c1, n.c2);
    GET DIANOSTICS rs = RETURN_STATUS;
    VALUES(CASE WHEN rc < 0 THEN RAISE_ERROR('70001', 'PROC CALL failed'));
    END@

    check :
    http://publib.boulder.ibm.com/infoce...d/t0011377.htm
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  3. #3
    Join Date
    Feb 2004
    Location
    Jaipur, India
    Posts
    40
    Hi Rahul,

    Thanks for ur reply. Actually I am new to DB2. Can you spare few minutes to explain the example you have quoted. For example the When Clause and passing the parameters to the proc as n.c1 etc.

    Thanks & Regards,

    Amit Kanodia

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    The WHEN clause contains a predicate which is evaluated first. If the predicate is evaluated to "true", then the trigger body is executed. Otherwise, the body is not executed. Essentually, this syntax is a left-over from the original implementation of triggers in DB2. IF statements achieve the same these days.

    And as usual, have a look at the DB2 manuals because they explain the syntax and their semantics (although it is not always simple to find the right place): http://publib.boulder.ibm.com/infoce...n/r0000931.htm
    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
  •