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 customerSET cus_addr = '<undefined-after-update>'WHERE customer_id = p_customer_id;END PROCEDURE;EXECUTE IMMEDIATE "CREATE TRIGGER t_customer_update_1UPDATE OF cus_addr ON customerREFERENCING NEW AS postFOR EACH ROWWHEN (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 1Near 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?
Thank you Andreas!
Works for me.Seb