Results 1 to 9 of 9
  1. #1
    Join Date
    May 2010
    Posts
    5

    Unanswered: having trouble with "before insert" trigger

    I have created a before update trigger that I would like to use to prevent null values in a column that has unique values. In case this value is not submitted at the insertion I would like the trigger to write in that column the ID value which is the primary key of the table. Both columns are of the same type - bigint

    CREATE TRIGGER TRIGG_1 BEFORE INSERT ON MY_TABLE
    REFERENCING NEW AS new_row FOR EACH ROW MODE DB2SQL
    WHEN ( new_row.UNIQUE_FIELD IS NULL )
    BEGIN ATOMIC
    SET new_row.UNIQUE_FIELD = new_row.P_ID;
    END

    It doesnt work and I have no idea why. I tried to put a "SIGNAL" statement into the "WHEN" section to see if it isnt being skipped and I actually got the signal message after running the insert query when the UNIQUE_VALUE was null...

    It is a DB2 Express-c database
    DB2version: "DB2 v9.7.100.177", "s091114", "IP23028", and Fix Pack "1".

    I will be very grateful for any help...
    Last edited by matteo84; 05-13-10 at 16:05.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by matteo84 View Post

    It doesnt work and I have no idea why.
    Define "doesn't work".

  3. #3
    Join Date
    May 2010
    Posts
    5
    Sorry

    I don't get any errors... The entry is written to the database and the UNIQUE_VALUE column is NULL...

    This way when I have two entries with NULL in the UNIQUE_VALUE column the insertion fails, because of the unique constraint on this column...

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    P_ID wouldn't be an IDENTITY column by any chance?

    Andy

  5. #5
    Join Date
    May 2010
    Posts
    5
    Quote Originally Posted by ARWinner View Post
    P_ID wouldn't be an IDENTITY column by any chance?

    Andy
    Hmm, no...

    It is not generated as an identity.

    I migrated from PostgreSQL to DB2 using the IBM Migration tool... It created the ddl automatically...

    Maybe this could cause the problem:
    (it is from the ddl file after the create statement which is ok.... but I'm not sure about this ALTER statement... I actually don't know what it does...

    ALTER TABLE MY_TABLE
    DATA CAPTURE NONE
    PCTFREE 0
    LOCKSIZE ROW
    APPEND OFF
    NOT VOLATILE
    DEACTIVATE VALUE COMPRESSION
    COMPRESS NO
    DROP RESTRICT ON DROP;

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Can you post the DDL of the table and the insert statement that you are using?

    Andy

  7. #7
    Join Date
    May 2010
    Posts
    5
    Quote Originally Posted by ARWinner View Post
    Can you post the DDL of the table and the insert statement that you are using?

    Andy
    Here is the creation DDL.. I removed the unique constraint.

    The column 'UNIQUE_VALUE' is the column CNUM

    An example of an insert query which should put 1234567890 into the column CNUM:
    insert into "PUBLIC".cp2_contact(P_ID,NAME, SURNAME) VALUES (1234567890, 'Bla', 'Bla');

    CREATE TABLE CONTACT (
    P_ID BIGINT NOT NULL,
    NAME VARCHAR(120),
    SURNAME VARCHAR(120),
    SEX VARCHAR(4),
    DBIRTH DATE,
    STREETNO VARCHAR(20),
    STREET VARCHAR(254),
    ZIP VARCHAR(10),
    CITY VARCHAR(40),
    CNUM BIGINT,
    CNTRY VARCHAR(60),
    PHONE VARCHAR(30),
    GSM VARCHAR(30),

    CONSTRAINT PK_CONTACT
    PRIMARY KEY (P_ID)
    )
    DATA CAPTURE NONE
    IN TS8;

    ALTER TABLE CONTACT
    DATA CAPTURE NONE
    PCTFREE 0
    LOCKSIZE ROW
    APPEND OFF
    NOT VOLATILE
    DEACTIVATE VALUE COMPRESSION
    COMPRESS NO
    DROP RESTRICT ON DROP;

    CREATE INDEX PUBLIC.IX10_CONTACT
    ON CONTACT
    (NAME, SURNAME)
    PAGE SPLIT SYMMETRIC
    COMPRESS NO
    ALLOW REVERSE SCANS;

    CREATE INDEX PUBLIC.IX11_CONTACT
    ON CONTACT
    (NAME, STREETNO, SURNAME, ZIP)
    PAGE SPLIT SYMMETRIC
    COMPRESS NO
    ALLOW REVERSE SCANS;

    CREATE INDEX PUBLIC.IX12_CONTACT
    ON CONTACT
    (NAME, SURNAME, ZIP)
    PAGE SPLIT SYMMETRIC
    COMPRESS NO
    ALLOW REVERSE SCANS;

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I am not sure what the problem is but try this:

    Code:
    CREATE TRIGGER TRIGG_1 BEFORE INSERT ON MY_TABLE
    REFERENCING NEW AS new_row FOR EACH ROW MODE DB2SQL
    SET new_row.UNIQUE_FIELD = coalesce(new_row.UNIQUE_FIELD,new_row.P_ID,-1);
    The -1 should be unnecessary, but put it in for now to see what happens.

    Andy

  9. #9
    Join Date
    May 2010
    Posts
    5
    Quote Originally Posted by ARWinner View Post
    I am not sure what the problem is but try this:

    Code:
    CREATE TRIGGER TRIGG_1 BEFORE INSERT ON MY_TABLE
    REFERENCING NEW AS new_row FOR EACH ROW MODE DB2SQL
    SET new_row.UNIQUE_FIELD = coalesce(new_row.UNIQUE_FIELD,new_row.P_ID,-1);
    The -1 should be unnecessary, but put it in for now to see what happens.

    Andy
    GREAT! It works... Thank you very much!!! You made my day

Posting Permissions

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