Set a default value with UPDATE trigger
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_1UPDATE 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 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?
Seb
------------------------------
Sebastien FLAESCH
------------------------------
#Informix
How about this:
create procedure ret_varchar(p varchar(255)) returning varchar(255); return p; end procedure;
CREATE TRIGGER t_customer_update_1UPDATE 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
------------------------------
Thank you Andreas! Works for me. Seb ------------------------------ Sebastien FLAESCH ------------------------------