Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2011
    Posts
    334

    Unanswered: use dynamic sql in a before trigger

    I want to use dynamic sql in a before trigger:
    part of the code is something like this :

    set sql = 'select count(*) from t1 where 1 = 1 '
    if n.c2 is null then
    set sql = sql + 'and c2 is null';
    else
    set sql = sql + 'and c2 = n.c2';
    end if;

    if n.c3 is null then
    set sql = sql + 'and c3 is null';
    else
    set sql = sql + 'and c3 = n.c3';
    end if;

    then excute sql dynamicly and put the result into a int variable.

    Dose anybody know how to execute a dynamic statement in a trigger
    without using cursor?

    for the reason why i want to do it ,plz reference the thead :

    http://www.dbforums.com/db2/1677405-...m-dpf-env.html

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I think dynamic SQL is not neccesary.
    Please try Example 2.

    Followings are the explanations.

    Quote Originally Posted by tonkuma View Post
    "If <cond-1> Then <cond-2>"
    would be replaced by
    "<cond-1> AND <cond-2>"

    "If <cond-1> Then <cond-2> Else If <cond-3> Then <cond-4>"
    would be replaced by
    "<cond-1> AND <cond-2> OR <cond-3> AND <cond-4>"

    so on...

    Then try the query by adding the following conditions to the outmost(final) WHERE condition.
    Code:
       AND
      (    :ws-order = 'A'
       OR  :ws-order = 'B'
       AND T2.POLICY_CAP >  0
       OR  :ws-order = 'C'
       AND T2.POLICY_CAP <= 0
       OR  :ws-order = 'D'
       AND T1.POLICY_VAL < T2.POLICY_CAP
       OR  :ws-order = 'E'
       AND T1.POLICY_VAL > T2.POLICY_CAP
      )
    Then your query might be like...
    Example 1:
    Code:
    SELECT COUNT(*)
     FROM  t1 t
     WHERE
      (    n.c2 IS NULL
       AND t.c2 IS NULL
       OR
           n.c2 IS NOT NULL
       AND t.c2 = n.c2
      )
       AND
      (    n.c3 IS NULL
       AND t.c3 IS NULL
       OR
           n.c3 IS NOT NULL
       AND t.c3 = n.c3
      )
    Consider more...
    If "t.c2 = n.c2" was true n.c2 should not be null.
    So, "n.c2 IS NOT NULL" must be not necessary.
    Same for n.c3.

    Example 2:
    Code:
    SELECT COUNT(*)
     FROM  t1 t
     WHERE
      (    n.c2 IS NULL
       AND t.c2 IS NULL
       OR  t.c2 = n.c2
      )
       AND
      (    n.c3 IS NULL
       AND t.c3 IS NULL
       OR  t.c3 = n.c3
      )

  3. #3
    Join Date
    Nov 2011
    Posts
    334
    Thanks a lot , tokuma.
    I have already used it in my trigger it works fine.

Posting Permissions

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