Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2008
    Posts
    2

    create sequence and create trigger

    Hello, the below statement works well on oracle ,please can someone help on how to create the below statement successfully on informix database.

    CREATE SEQUENCE Y;

    CREATE TRIGGER smou_auto BEFORE INSERT on smou
    for each row
    when (new.id is null)
    begin
    SELECT y.nextval INTO :new.id FROM DUAL;
    end;
    /

  2. #2
    Join Date
    Mar 2008
    Posts
    2
    There will be no need to create the sequence and there will be no need to create trigger , creating the table intended to use the sequence in the below manner will give the solution expected from the sequence and trigger

    create table smou
    (id SERIAL not null,
    name varchar(30) default NULL,
    time varchar(100) default NULL);


    "id SERIAL not null" this makes informix provides every inserted record
    with a unique number, so there is no need to create sequence or trigger.

  3. #3
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    unfortunately , with IDS is not possible to execute any command differ of INSERT,DELETE,UPDATE,EXECUTE PROC/FUNC inside a Trigger.

    You need create a SPL to get the nextval .

    Code:
    CREATE SEQUENCE Y;
    
    create procedure sp_smou_auto() returning integer
      return y.nextval ;
    end procedure
    ;
    CREATE TRIGGER smou_auto  INSERT on smou
    referencing new as n
    for each row
    when (n.id is null) (
    execute procedure sp_smou_auto() into id 
    )
    ;
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •