Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2011
    Posts
    11

    Unanswered: Trigger question

    I'm new, very new to DB2, so please excuse if this seems like an ignorant question.

    I'm trying to create a trigger that updates after an insert. It looks like this:

    CREATE OR REPLACE TRIGGER IDENTITY_LOCATOR AFTER insert on IDENTITY_COLUMNS referencing NEW AS N FOR EACH ROW UPDATE IDENTITY_COLUMN SET SEQUENCE_NAME = (SELECT SEQNAME FROM syscat.sequences SN,SYSCAT.COLIDENTATTRIBUTES CN WHERE CN.SEQID = SN.SEQID AND CN.TABNAME = N.TABLE_NAME) WHERE IDENTITY_COLUMNS.TABLE_NAME = N.TABLE_NAME

    Now, when I manually insert from the command line the trigger works fine and updates the field. Also, If I set the trigger to UPDATE without the SELECT the trigger works too:

    CREATE OR REPLACE TRIGGER IDENTITY_LOCATOR AFTER insert on IDENTITY_COLUMN referencing NEW AS N for each row UPDATE IDENTITY_COLUMN SET SEQUENCE_NAME = 'RANDOMSTRING' WHERE IDENTITY_COLUMN.TABLE_NAME = N.TABLE_NAME

    Am I missing something completely obvious? I've tried using begin/begin atomic and if inserting then in various ways. Nothing seems to work. So I'm breaking down and asking for help!

    Thanks in advance.

  2. #2
    Join Date
    Oct 2011
    Posts
    11

    Follow up

    After scanning the site a little bit I found a post where someone resolved a similar problem with something that looked like:

    CREATE OR REPLACE TRIGGER IDENTITY_LOCATOR AFTER insert on IDENTITY_COLUMN referencing NEW AS N for each row begin atomic DECLARE seq_placeholder varchar(255); set seq_placeholder = (SELECT SEQNAME FROM syscat.sequences SN,SYSCAT.COLIDENTATTRIBUTES CN WHERE CN.SEQID = SN.SEQID AND CN.TABNAME = N.TABLE_NAME); UPDATE IDENTITY_COLUMN SET SEQUENCE_NAME = seq_placeholder WHERE IDENTITY_COLUMN.TABLE_NAME = N.TABLE_NAME;end

    Do I need to somehow quote the seq_placeholder value even though its declared as varchar?

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    (1) What are your DB2 version/release and platform OS?

    (2) What are your questions/issues?

    (2-1) Did you received error message(s)?
    If so, is it at compile time or execution time?
    Copy and paste the messages including text and codes.

    (2-2) If result was different from your expectations.
    What are your expected result and actually received result?


    Am I missing something completely obvious?
    First trigger: AFTER insert on IDENTITY_COLUMNS
    Second trigger: AFTER insert on IDENTITY_COLUMN
    Third trigger: AFTER insert on IDENTITY_COLUMN

    Copy and paste your code exactly.
    If you want to hide your table names and/or column names, or some other objects,
    change your code carefully, not to loose consistency.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I remembered that I replied similar things to another person somedays ago.
    See 7th post(my fourth post) in this thread
    http://www.dbforums.com/db2/1670998-...ction-rrn.html


    And, I also found those were written in
    http://www.dbforums.com/db2/854783-m...e-posting.html
    ...

    Please follow these guidelines to get quick, apt and meaningful responses :

    1) Every question posted must include your DB2 Version, fixpack and Edition + your Operating System(including version info) + info on any third party software you use.
    You can get his info using the following commands
    db2level -> to get db2 version and fixpack level
    db2licm -l -> to get the db2 type (WSE, ESE, etc)


    2) If you question is regaring an error, make sure you post the error code, corresponding message and the command/sql which caused the error.

    ...

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by oshman View Post
    After scanning the site a little bit I found a post where someone resolved a similar problem with something that looked like:

    CREATE OR REPLACE TRIGGER IDENTITY_LOCATOR AFTER insert on IDENTITY_COLUMN referencing NEW AS N for each row begin atomic DECLARE seq_placeholder varchar(255); set seq_placeholder = (SELECT SEQNAME FROM syscat.sequences SN,SYSCAT.COLIDENTATTRIBUTES CN WHERE CN.SEQID = SN.SEQID AND CN.TABNAME = N.TABLE_NAME); UPDATE IDENTITY_COLUMN SET SEQUENCE_NAME = seq_placeholder WHERE IDENTITY_COLUMN.TABLE_NAME = N.TABLE_NAME;end

    Do I need to somehow quote the seq_placeholder value even though its declared as varchar?
    You only need to quote it if you reference a literal value, not if you select the value from a another table.

    When you run a script to create a Trigger or SP, it may (like yours) contain some intermediate command delimeters (. But there must only be one command delimeters in the create/repace command (at the end). I am not sure if this is your problem because you did not provide the error message details you are getting.

    Some client tools take care of this automatically, but you may have change your statement delimeter to @ and use it after the last END, so DB2 knows when your create/replace ends and when a line in your trigger ends.

    If using the command line editor, you can do this:
    db2 -td@ -vf script-name
    and put a @ after the last END statement in your create/replace command.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Oct 2011
    Posts
    11

    Thanks for the help

    Ok, the basics - I'm running DB2 v9.7.4 Express-C on linux.

    Table being inserted on:

    ~$ db2 "describe table identity_column"

    Data type Column
    Column name schema Data type name Length Scale Nulls
    ------------------------------- --------- ------------------- ---------- ----- ------
    TABLE_NAME SYSIBM VARCHAR 50 0 No
    COLUMN_NAME SYSIBM VARCHAR 10 0 Yes
    SEQUENCE_NAME SYSIBM VARCHAR 20 0 Yes

    Application inserts TABLE_NAME/COLUMN_NAME and trigger is supposed to update SEQUENCE_NAME.

    After an and insert on the table by the application this is what I see (SEQUENCE_NAME is '-' for all fields):

    ~$ db2 "select * from IDENTITY_COLUMN WHERE TABLE_NAME = 'BLOCKED_IPS'"

    TABLE_NAME COLUMN_NAME SEQUENCE_NAME
    -------------------------------------------------- ----------- --------------------
    BLOCKED_IPS IID -

    However, if I remove this record and insert manually from the command line

    ~$ db2 "DELETE FROM IDENTITY_COLUMN WHERE TABLE_NAME = 'BLOCKED_IPS'"
    DB20000I The SQL command completed successfully.

    ~$ db2 "INSERT INTO IDENTITY_COLUMN (TABLE_NAME,COLUMN_NAME) VALUES ('BLOCKED_IPS','IID')"
    DB20000I The SQL command completed successfully.

    ~$ db2 "select * from IDENTITY_COLUMN WHERE TABLE_NAME = 'BLOCKED_IPS'"

    TABLE_NAME COLUMN_NAME SEQUENCE_NAME
    -------------------------------------------------- ----------- --------------------
    BLOCKED_IPS IID SQL111023151930150

    Here's some more info:

    ~$ db2level
    DB21085I Instance "demo" uses "32" bits and DB2 code release "SQL09074" with
    level identifier "08050107".
    Informational tokens are "DB2 v9.7.0.4", "s110330", "IP23242", and Fix Pack
    "4".
    Product is installed at "/opt/ibm/db2/V9.7".

    ~$ db2licm -l
    Product name: "DB2 Express-C"
    License type: "Unwarranted"
    Expiry date: "Permanent"
    Product identifier: "db2expc"
    Version information: "9.7"
    Max number of CPUs: "2"
    Max amount of memory (GB): "2"

    Like I said, I'm pretty new, so I'm not exactly sure where to look for trigger execution errors in logs.

    Also, if I create the trigger and set the update to a static value instead of a SELECT result the trigger does update the SEQUENCE_NAME field with that value.

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Sorry, I did not understand your original problem when I replied above. I am still not sure that I understand your problem and the following comment may be irrelevant to your issue; however, you should be aware that values in the syscat.sequences are not always current as sequences can be cached.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Oct 2011
    Posts
    11

    Thank You!

    Thanks so much Marcus, you are 100% correct. As a test I used this trigger:

    CREATE OR REPLACE TRIGGER IDENTITY_LOCATOR AFTER insert on IDENTITY_COLUMN referencing NEW AS N for each row begin atomic DECLARE seq_placeholder varchar(255); set seq_placeholder = (SELECT TABLE_NAME FROM IDENTITY_COLUMN WHERE TABLE_NAME = N.TABLE_NAME); UPDATE IDENTITY_COLUMN SET SEQUENCE_NAME = seq_placeholder WHERE IDENTITY_COLUMN.TABLE_NAME = N.TABLE_NAME;end

    Not querying syscat.sequences at all but still selecting and updating values. Sure enough, the trigger worked properly and updated all fields! Good to know that the issue wasn't with how I wrote the query itself.

    I guess a few additional details would help here. The trigger will mostly be used only during install of an application and occasionally when a new table is created by the application. As a fall back, I can always update the field programmatically but figured the off load would be nice if it were possible.

    Do you have any other suggestions on how to overcome this before I fall back to doing it programmatically?

    Thanks again!

Posting Permissions

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