How to create trigger in informix?
11,353
Solution 1
I think you should write a stored procedure with the IF - THEN logic and call that from this trigger. I think triggers support only simple SQL statements, not SPL statements
edit:
CREATE PROCEDURE proc1()
REFERENCING OLD AS o NEW AS n FOR tab1;
....your logic
CREATE TRIGGER adr_trg update of fname, lname on adr
REFERENCING OLD AS o NEW AS n
FOR EACH ROW(EXECUTE PROCEDURE proc1() WITH TRIGGER REFERENCES);
Solution 2
Old question, but not well answered, in case anyone lands up here like I did.
Informix handles the requested functionality, but the syntax is different:
create trigger adr_trg update of fname,lname on adr
referencing old as o new as n
for each row
when ( o.fname != n.fname )
(
insert into adrlog (old_value,new_value)
values (o.fname,n.fname)
) ,
when ( o.lname != n.lname )
(
insert into adrlog (old_value,new_value)
values (o.lname,n.lname)
)
;
Author by
PeterP
Updated on June 04, 2022Comments
-
PeterP almost 2 years
I am trying to write a trigger like in the following simplified example:
create trigger adr_trg update of fname, lname on adr REFERENCING OLD AS o NEW AS n FOR EACH ROW ( IF o.fname <> n.fname THEN insert into adrlog (old_value, new_value) values (o.fname, n.fname); END IF; IF o.lname <> n.lname THEN insert into adrlog (old_value, new_value) values (o.lname, n.lname); END IF; )
This fails!
Only this works:
create trigger adr_trg update of fname, lname on adr REFERENCING OLD AS o NEW AS n FOR EACH ROW ( insert into adrlog (old_value, new_value) values (o.fname, n.fname); )
What am I doing wrong?
-
RET over 12 yearsA good tradesman never blames his tools. It seems to me that the conditional logic you require is best placed in a procedure. It may prove to be sufficiently generic that it can be used for multiple tables, in an OO fashion. Your simplified example certainly suggests this.
-
Lukas Eder over 9 years