Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2012
    Posts
    4

    Unanswered: Before Insert Trigger for Primary Keys

    I am trying to create a before insert trigger to populate the primary key for a table. I am using DB2 for z/OS v9.1. This is what I have:

    Code:
    CREATE TRIGGER tgr_student_bi
         NO CASCADE BEFORE INSERT ON STUDENT
         REFERENCING NEW AS new_row
         FOR EACH ROW MODE DB2SQL
              BEGIN ATOMIC
                   SET (new_row.SID) = (NEXTVAL FOR STUDENT_ID_SEQ);
              END $
    
    INSET INTO STUDENT (SALUTATION, FNAME, LNAME, ...)
    VALUES ('Mr.', 'First Name', 'Last Name', ...) $
    In the trigger I need to use a sequence "STUDENT_ID_SEQ" to populate the primary key "SID". The trigger deploys fine but does not activate and populate the primary key. I keep getting an error

    Code:
    AN UPDATE, INSERT, OR SET VALUE IS NULL, BUT THE OBJECT COLUMN SID CANNOT CONTAIN NULL VALUES. SQLCODE=-407, SQLSTATE=23502, DRIVER=4.12.79
    I understand what this error is saying, because I omitted the primary key in the insert statement (which cannot be null) this error was produced. But what gets me is why didn't the trigger fire and populate the primary key for this insert statement, it deployed fine and I can see it in my schema? Does the trigger need to be re-structured in order to fire before the error occurs?

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Can't you use NEXTVAL in the INSERT statement directly?

  3. #3
    Join Date
    Apr 2012
    Posts
    4
    Quote Originally Posted by n_i View Post
    Can't you use NEXTVAL in the INSERT statement directly?
    Well, yes I can and it did work... however for my assignment I need to create the before insert trigger to not only populate the primary key but a few other elements of the table as well. I was trying to get the primary key to populate first then add the other values that need to be populated (such as USER and CURRENT DATE in other fields) I did try what you suggested in the insert statement and add "SET new_row.CREATED_BY = USER" it did work, but I am required to use a trigger to do the job though...

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You could create a view, insert into the view and an INSTEAD OF trigger on the view drives the actual INSERT statement into the base table. That nested INSERT statement can include the NEXTVAL. Not really that nice, but it should give you some sort of solution.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Apr 2012
    Posts
    4
    Quote Originally Posted by stolze View Post
    You could create a view, insert into the view and an INSTEAD OF trigger on the view drives the actual INSERT statement into the base table. That nested INSERT statement can include the NEXTVAL. Not really that nice, but it should give you some sort of solution.
    That is a thought, but I think I found my problem. Since the NOT NULL constraint on the primary key was causing the error before the trigger was fired (which should populate the primary key), I amended the INSERT statement to include erroneous values for the primary key (just something so the insert statement can pass the NOT NULL constraint) and then the trigger gets fired overwriting the erroneous value and populating the primary key with the next value for the sequence. Not exactly what I was looking for, but it works...

    I guess the lesson here is that constraints added to the table take precedence before anything else, even before "before insert" triggers.

    Thanks for your suggestions though!

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Can you please provide the list of statements/commands that worked?

    =
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by mahonri View Post
    [....]
    I guess the lesson here is that constraints added to the table take precedence before anything else, even before "before insert" triggers.
    In LUW before triggers are fired before any constraint enforcement, so perhaps this is different in Z? Example (9.7 express-c fp 2):

    db2 "create table t (x int not null primary key, y int not null)"
    db2 "create sequence s as int"
    db2 "create trigger trg before insert on t referencing new as n for each row set n.x = nextval for s"
    db2 "insert into t(y) values (3)"
    db2 "insert into t(y) values (3)"
    db2 "select * from t"

    X Y
    ----------- -----------
    1 3
    2 3

    2 record(s) selected.

    db2 alter table t drop primary key
    db2 "alter table t add constraint xyz unique ( x )"
    db2 "insert into t(y) values (3)"
    db2 "select * from t"

    X Y
    ----------- -----------
    1 3
    2 3
    3 3

    3 record(s) selected.
    Last edited by lelle12; 04-05-12 at 15:38.
    --
    Lennart

  8. #8
    Join Date
    Apr 2012
    Posts
    4
    Quote Originally Posted by lelle12 View Post
    In LUW before triggers are fired before any constraint enforcement, so perhaps this is different in Z?
    That is exactly what I was trying to do... but I kept getting my error.

    Quote Originally Posted by sathyaram_s View Post
    Can you please provide the list of statements/commands that worked?
    What I did was use the exact trigger that I have posted above "tgr_student_bi" and altered the insert statement to:

    INSET INTO STUDENT (SID, SALUTATION, FNAME, LNAME, ...)
    VALUES (1, 'Mr.', 'First Name', 'Last Name', ...) $

    This insert statement has an SID being sent to the database with a value of "1" but before the data actually gets inserted the trigger fires and changes the value of "1" to the next value in the sequence "STUDENT_ID_SEQ". When I selected the data in the table I got:

    SID SALUTATION FNAME LNAME
    ----------- ----------- ----------- -----------
    710 Mr. First Name Last Name

    The sequence was supposed to start at 700 and increment by 10, I preformed a test simply inserting data without the trigger (for the primary key) once before to be sure there wasn't a problem with the sequence or the syntax to get the next value for the sequence, so that is the reason it is 710 and not 700...
    Last edited by mahonri; 04-05-12 at 21:41. Reason: respond to another post as well...

Posting Permissions

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