Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2002
    Location
    Recife - Brazil
    Posts
    4

    Unhappy Unanswered: Trigger and sequence

    How can I make a trigger in DB2 v 5.2 Win NT to create a sequence on INSERT (autoinc field)?

    Please I need it urgent !!!!

    Neiva

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Trigger and sequence

    If I remember right, there was no autoinc column with DB2 V5.2 ...

    HTH

    Sathyaram

    Originally posted by Neiva
    How can I make a trigger in DB2 v 5.2 Win NT to create a sequence on INSERT (autoinc field)?

    Please I need it urgent !!!!

    Neiva

  3. #3
    Join Date
    Nov 2002
    Location
    Recife - Brazil
    Posts
    4

    Exclamation

    Yes, I know , but how can I make it? With a trigger ? Cretaing a sequence in the programs?

    N

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    A procedure I have in my notes ... This is not my own ... I found it in a newsgroup posting ...


    HTH

    Cheers

    Sathyaram

    You cannot create an identity column per-se, however you can use a trigger to
    achieve the desired behavior.

    First: Create the "identity" column with a DEFAULT and best a unique constraint.
    Second: Create a counter function using a scratch pad. You'll find this function
    in sqllib/samples.
    Third: Create a before insert for each row trigger on the table that fetches the
    current maximum of the identity column and adds an offset using the counter
    function.
    If you have any other tables where you want to use the generated value, you can
    do this in
    the trigger body (BEGIN ATOMIC SET new.identity = .....; <do_more_stuff>; END.
    Fourth: For isolation level you can experiment with cursor stability and higher.
    E.g. you can handle conflicts in your application and reapply the insert or you
    can choose
    a higher isolation level up to shared table lock if you want DB2 to handle it.
    You should commit ASAP after the insert to free up the locks.

    Note: If you only do single row inserts you don't need the scratch pad function.

    Example (found in this newsgroup):
    CREATE FUNCTION NULLID.COUNTER() RETURNS INT EXTERNAL NAME
    'DB2Udf!ctr' LANGUAGE JAVA PARAMETER STYLE DB2GENERAL NO SQL NOT
    DETERMINISTIC NOT FENCED SCRATCHPAD NO EXTERNAL ACTION;

    CREATE TRIGGER NULLID.ADDRESSAUTOINC NO CASCADE BEFORE INSERT ON
    NULLID.ADDRESSES REFERENCING NEW AS n FOR EACH ROW MODE DB2SQL BEGIN ATOMIC
    SET (n.ADDR_UID ) = (SELECT value(MAX(ADDR_UID),0) +NULLID.COUNTER() FROM
    NULLID.ADDRESSES); END


    Originally posted by Neiva
    Yes, I know , but how can I make it? With a trigger ? Cretaing a sequence in the programs?

    N

  5. #5
    Join Date
    Nov 2002
    Location
    Recife - Brazil
    Posts
    4
    First thanks for your help.

    After I read your tip I try the following TRIGGER

    CREATE TRIGGER tester NO CASCADE BEFORE INSERT ON
    TESTE REFERENCING NEW AS n FOR EACH ROW MODE DB2SQL BEGIN ATOMIC
    SET (n.NUMERO ) = (SELECT value(MAX(NUMERO),0) + 1 FROM
    TESTE);END

    Where NUMERO is a column in the table (integer not null)

    So the sequence was created correctly !

    SN

Posting Permissions

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