Help with trigger - URGENT!

Collapse
This topic is closed.
X
X
 
  • Time
  • Show
Clear All
new posts
  • Remove the obvious for replies

    Help with trigger - URGENT!

    I am using DB2 8.1.4 with SDE and Info Integrator. I am trying to
    create a trigger to populate a spatial table based on values from an
    attribute table. However, it appears to not recognize the CASE
    condition. Here's the trigger :

    create trigger MDA.TUA_AMNGROW NDIR after update
    of UTM8315N,
    UTM8315E
    on MDA.MNGROWNDIR
    referencing new as new_upd for each row mode db2sql
    begin atomic
    UPDATE DB2ADMIN.AUX_PT
    SET shape =
    CASE WHEN (new_upd.UTM831 5E IS NULL OR
    new_upd.UTM8315 N IS NULL ) THEN NULL
    ELSE

    DB2GSE.ST_POINT (new_upd.UTM831 5E,new_upd.UTM8 315N,5)
    END
    WHERE OBJECTID = new_upd.OBJECTI D;
    end%

    What is supposed to happen is that if either the utm8315e or utm8315n
    values are null, then the field name SHAPE in the spatial table should
    be set to null. Otherwise, use the function gse.st_point() and update
    the shape field with the results of the function (note: the function
    cannot have null values in the parms).

    If I try to set either utm8315e or utm8315n to null then, according to
    the error, the db2gse.st_point is still being called so it would
    appear that either the NULL condition is not being recognized or the
    ELSE statement is firing every time.

    I have tried to replace the else with NOT null consitions and the same
    occurs. I have also tested the statement UPDATE db2admin.aux_pt set
    shape = NULL where objectid = 2 and that woks OK (so shape is indeed
    nullable)

    Any ideas ?

Working...