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