Informix: Force values into columns in FOR EACH ROW trigger event
Posted in 2026
Greetings, Family.
My user requires triggers on insert and update that would force values into some columns, no matter what the user's INSERT or UPDATE values were. A default "constraint" won't accomplish this because it can be overridden just by supplying a value for the column.
I tried this:
create trigger yodel_ins_trig
insert on yodel referencing new as post
for each row
(
update post set (ins_date, ins_user) = (TODAY, USER)
),
when (other conditions)
(do other stuff on other tables);
I got error -734 on that update: Object name matches old or new values correlation name.
(Or was that error -747, a similar objection?)
It appears from the 12.10 Guide to SQL Syntax manual, page 2-352, that this is explicitly VERBOTTEN!
I found other syntax as well on StackOverflow, where our dear friend Jonathan L had a comment. For example:
for each row
( let post.ins_date = TODAY,
let post.ins_user = USER);
But the "let" was a syntax error. I also tried just assigning them without the "let"; also syntax error.
One suggestion I found was to write a SPL, like force_user_date() and, in the trigger code:
execute force_user_date() into (ins_date, ins_user) but I just need to see a sample of trigger that works for this functionality.
Any such samples or better ideas out there?
Köszönöm szépen. (No, I never really picked up Hungarian. :-)
------------------------------
+-----------------------------------------------------------+
| I am pleased to report that I had no problems today. |
| I had only issues, opportunities, challenges and valuable |
| learning experiences. |
+------------------------------------------ Jacob S --------+
------------------------------
You do need to use a procedure and to call that procedure with triggers. The trigger needs to use "WITH TRIGGER REFERENCES" and the procedure needs to have a referencing clause.
Try this:
create table myvals(id serial, val char(1), ins_date date, ins_user char(20));
CREATE PROCEDURE sp_myvals_set()
REFERENCING NEW AS n FOR myvals;
LET n.ins_date = TODAY;
LET n.ins_user = USER;
END PROCEDURE;
CREATE TRIGGER myvals_ins
INSERT ON myvals
FOR EACH ROW
(EXECUTE PROCEDURE sp_myvals_set()
WITH TRIGGER REFERENCES);
CREATE TRIGGER myvals_upd
UPDATE ON myvals
FOR EACH ROW
(EXECUTE PROCEDURE sp_myvals_set()
WITH TRIGGER REFERENCES);
insert into myvals (id, val, ins_date, ins_user) values (0, "A", "1/1/2000", "myuser");
insert into myvals (id, val, ins_date, ins_user) values (0, "B", "1/1/2000", "myuser");
update myvals set val="X", ins_date="1/1/1999", ins_user="mike" where val="B";
select *
from myvals;
Results:
id val ins_date ins_user
1 A 11/11/2026 informix
2 X 11/11/2026 informix
------------------------------
Mike Walker
xDB Systems, Inc
www.xdbsystems.com
------------------------------
Hi Jacob. See also: https://www.oninitgroup.com/faq-items/informix-trigger-updating-same-table <https://www.oninitgroup.com/faq-items/informix-trigger-updating-same-table> ------------------------------ Doug Lawry Oninit Consulting ------------------------------
Doug,You article saved the day for me. Literally - a day's work! I wrote the triggers with accompanying SPL and it compiled correctly. If it works the way [I think] my user wants it to remains to be seen. But as I wrote it, it should undo any attempt by the user to put fake insert time/user in there. Or, for that matter, update tim/user.Still, that's, that's a big if. (Have I made that joke on the forum already?) +----- Jacob Salomon --------------------------+