Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Location
    Iran
    Posts
    1

    Unanswered: Befor insert trigger and Sequence

    Dear Members

    I have a table called tb_user and.i wrote a before insert trigger for this table which fill the user_code field for this table from a sequence.Any thing works well.

    But, now i want to get current value of this sequence from ado recordset.
    How can i configure my ado connection or ado recordset that when i call addnew in recordset, the befor insert trigger fires and i get the current value ?this must be happen before update method of recordset.

    create or replace trigger tib_user
    BEFORE UPDATE OR INSERT OR DELETE ON tb_user
    FOR EACH ROW
    begin
    If inserting Then
    :new.createuid := AAA.getUserId();
    :new.updateuid := AAA.getUserId();
    :new.createdt:=AAA.GetCurDateTime();
    :new.updatedt:=AAA.GetCurDateTime();
    select S_TB_USERCODE.nextval into :New.User_Code from dual;
    end if

  2. #2
    Join Date
    Nov 2003
    Location
    Rotterdam, Netherlands
    Posts
    127
    Hi,
    It is not a good idea to write a trigger to generate a sequence number.
    And is not possible to take it directly in your case.
    What i suggest is that YOU take the next val into a recordset directly without a trigger.
    i.e "SELECT SEQ.NEXTVAL FROM DUAL" IN A DIFFERENT RECORDSET.

    HOpe i was clear enough....

  3. #3
    Join Date
    Sep 2003
    Posts
    156

    Re: Befor insert trigger and Sequence

    Originally posted by h_abangar
    Dear Members

    I have a table called tb_user and.i wrote a before insert trigger for this table which fill the user_code field for this table from a sequence.Any thing works well.

    But, now i want to get current value of this sequence from ado recordset.
    How can i configure my ado connection or ado recordset that when i call addnew in recordset, the befor insert trigger fires and i get the current value ?this must be happen before update method of recordset.

    create or replace trigger tib_user
    BEFORE UPDATE OR INSERT OR DELETE ON tb_user
    FOR EACH ROW
    begin
    If inserting Then
    :new.createuid := AAA.getUserId();
    :new.updateuid := AAA.getUserId();
    :new.createdt:=AAA.GetCurDateTime();
    :new.updatedt:=AAA.GetCurDateTime();
    select S_TB_USERCODE.nextval into :New.User_Code from dual;
    end if
    First of all use ' create sequence seq_name
    start with 1
    increment by 1;

    then you can use pre-insert triggers to:

    SELECT seq_name.NEXTVAL
    INTO :tb_user.id
    FROM SYS.dual;

    to find the current value, or next value of the sequence you can use .currval or .nextval from dual
    rgs,

    Ghostman

Posting Permissions

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