Topic
1 reply Latest Post - ‏2009-07-21T13:42:26Z by SystemAdmin
gmontero
gmontero
1 Post
ACCEPTED ANSWER

Pinned topic Use of sequences withing triggers in solidDB?

‏2009-06-29T16:19:13Z |
I'm trying to see if I can update a column with a sequences NEXTVAL with a trigger set to be
called prior to an insert call.

I have not had much luck. I can't seem to reference the sequence name in the trigger, even
taking a stab at using a referencing clause for it. I even tried to create a sequence within
the trigger.

Looking at the syntax diagram in the InfoCenter, I think I'm coming to the conclusion that
I can only assign literal values to a column.

Is this correct, or can someone provide an example of referencing a sequence within a trigger
to update a column?

thanks for any help,
gabe
Updated on 2009-07-21T13:42:26Z at 2009-07-21T13:42:26Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    115 Posts
    ACCEPTED ANSWER

    Re: Use of sequences withing triggers in solidDB?

    ‏2009-07-21T13:42:26Z  in response to gmontero
    Try the following example:

    DROP TABLE PEOPLE;
    COMMIT WORK;

    CREATE TABLE PEOPLE (ID INTEGER NOT NULL PRIMARY KEY, NAME VARCHAR);
    COMMIT WORK;
    DROP SEQUENCE SEQ1;
    COMMIT WORK;

    CREATE SEQUENCE SEQ1;
    COMMIT WORK;
    DROP TRIGGER TRIG1;
    COMMIT WORK;

    "CREATE TRIGGER TRIG1
    ON PEOPLE
    BEFORE INSERT
    REFERENCING NEW id as new_id
    BEGIN

    EXEC SQL WHENEVER SQLERROR ABORT;

    EXEC SEQUENCE SEQ1.NEXT INTO NEW_ID;

    END";
    COMMIT WORK;

    insert into people (name) values ('Fred');
    commit work;