×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Help with triggers

Help with triggers

Help with triggers

(OP)
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);

RE: Help with triggers

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 = "0" || 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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close