Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2011
    Posts
    13

    Unanswered: default values vs formula

    running: db2 luw 9.7

    I have a general query on the setup of a column...

    I have 3 columns (all varchar):

    Type Model Description

    Type and model are required, I would like Description not to be required, but by default be filled with Type concat Model, so, if I filled with

    "9406" "520" "This is a 9406 model 520"
    that would work..
    but if I filled with
    "9406" "520" it would fill the description with 9406520 automatically.

    Is that possible? I can put a formula in, but then the DDL shows "GENERATED ALWAYS AS.....". I don't want it generated always.

    Thanks in advance

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Did you try generated by default? Or you could use a trigger to populate if the value was null after insert and after update.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    There is no GENERATED BY DEFAULT for an expression. You will have to use BEFORE triggers to do it, not after.

    Andy

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Excuse my ignorance about triggers, but why can't you use an AFTER trigger based on if the column has a null value?

    Also, on the generated always, what about using the coalesce statement to populate the column with generated always? Would that work?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    After triggers are actions you take AFTER the data has been updated in the table, so how can you change the value of a column once it has been written? That is what BEFORE triggers are for.

    I tried using the COALESCE idea. Unfortunately you cannot use the column that you are populating in the expression, so that will not work. It looks like the BEFORE trigger is the only way to go.

    Andy

Posting Permissions

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