Help with triggers

[sridhart]

I need help on trigger, I am trying to add a default value if the new value is null.
for ex: tab1(col1 varchar2(10), col2 datetime, col3 decimal(5,0))
I want a trigger to execute after insert of each row if col3 is null, then default both col2 = current year to sec and col3 = 100.
I am new to informix and dont know much about triggers, any help would be appreciated.
We use informix 7.31 on a aix box.
I have written something but doesnt work,

CREATE TRIGGER TRG_CC_CIVIL_AI
INSERT ON CC_CIVIL_HEADER
REFERENCING NEW AS NEW
AFTER FOR EACH ROW
( UPDATE CC_CIVIL_HEADER
SET CREATION_DATE = CURRENT,
CREATED_BY = 101
WHERE CAUSE = NEW.CAUSE
AND CREATION_DATE IS NULL);

 

[bjpandya]

Hey buddy !!
I was just like you when I joined .
well you can use the trigger stored procedure combination to do what you want
just like this,
create trigger "informix".ins_session insert on "informix".session
referencing new as post_ins
for each row
(
execute procedure "informix".add_session(post_ins.unikix_port
,post_ins.program_id ,post_ins.group_number ,post_ins.employee_number
) into "informix".session.h_pk,"informix".session.h_grp_emp_prg);


create dba procedure "informix".add_session(un_port char(4), prog_id char(4),grp_no smallint,emp_no integer)
returning char(15), char(11);

define s_h_pk char(15);
define s_h_grp_emp_prg char(11);
define s_grp_emp char(7);
define s_emp_no char(5);

if (emp_no > 9999) then
let s_emp_no = emp_no;
elif (emp_no > 999) then
let s_emp_no = "0" || emp_no;
elif (emp_no > 99) then
let s_emp_no = "00" || emp_no;
elif (emp_no > 9) then
let s_emp_no = "000" || emp_no;
else
let s_emp_no = "0000" || emp_no;
end if

if (grp_no < 10) then
let s_grp_emp = &quot;0&quot; || grp_no || s_emp_no;
else
let s_grp_emp = grp_no || s_emp_no;
end if
let s_h_pk = un_port || prog_id || s_grp_emp;
let s_h_grp_emp_prg = s_grp_emp || prog_id;

return s_h_pk, s_h_grp_emp_prg;

end procedure;

____________

I am sending you a set of stored proc. trigger ucan use that to solve problem if you need specifc answer write me at bjpandya@home.com with detail description and datatypes used in the tables.............

Enjoy.