Informix

 View Only
  • 1.  Set a default value with UPDATE trigger

    Posted Wed July 20, 2022 06:33 AM

    Hello!

    I must be missing something obvious.

    Is there a simple way in Informix SPL/triggers to replace a value with an UPDATE FOR EACH ROW trigger?

    For example, to set a default value when the updated value is empty/NULL?

    I have tried this, but get a syntax error:

    CREATE TRIGGER t_customer_update_1
      UPDATE OF cus_addr ON customer
      REFERENCING NEW AS post
      FOR EACH ROW
         WHEN (LENGTH(post.cus_addr)=0) (post.cus_addr = '<undefined>')

    When using a stored procedure to execute an UPDATE to change the column value:

    CREATE PROCEDURE p_customer_update_1( p_customer_id INTEGER )
    UPDATE customer
    SET cus_addr = '<undefined-after-update>'
    WHERE customer_id = p_customer_id;
    END PROCEDURE
    ;

    EXECUTE IMMEDIATE "CREATE TRIGGER t_customer_update_1
    UPDATE OF cus_addr ON customer
    REFERENCING NEW AS post
    FOR EACH ROW
    WHEN (LENGTH(post.cus_addr)=0)
    (EXECUTE PROCEDURE p_customer_update_1( post.customer_id ) )
    ;


    I get the error:

    > update customer set cus_addr=' ' where customer_id = 9003;

    747: Table or column matches object referenced in triggering statement.
    Error in line 1
    Near character position 1


    Which is kind of expected, as it could loop infinitely right?

    But then I wonder... is this even possible with Informix SPL/triggers?

    Seb



    ------------------------------
    Sebastien FLAESCH
    ------------------------------

    #Informix


  • 2.  RE: Set a default value with UPDATE trigger
    Best Answer

    IBM Champion
    Posted Wed July 20, 2022 07:36 AM
    How about this:

    create procedure ret_varchar(p varchar(255)) returning varchar(255); return p; end procedure;

    CREATE TRIGGER t_customer_update_1
    UPDATE OF cus_addr ON customer
    REFERENCING NEW AS post
    FOR EACH ROW
    WHEN (LENGTH(post.cus_addr)=0) (execute procedure ret_varchar('<undefined>') into cus_addr);

    ------------------------------
    Andreas Legner
    ------------------------------



  • 3.  RE: Set a default value with UPDATE trigger

    Posted Wed July 20, 2022 07:54 AM

    Thank you Andreas!

    Works for me.
    Seb



    ------------------------------
    Sebastien FLAESCH
    ------------------------------