Topic
3 replies Latest Post - ‏2012-11-30T20:26:32Z by SystemAdmin
SystemAdmin
SystemAdmin
3129 Posts
ACCEPTED ANSWER

Pinned topic Is there a way in SQL to get the current member of a table?

‏2012-11-26T14:39:29Z |
I'm trying to write a SQL trigger that will write a record in another table when a specific field is updated. The basics of this work fine, the issue is that I really only want to watch that field in just one of the table's members. The trigger fires (and writes out a record) when the field is updated in any member. If I cannot focus the trigger on a single member then (if possible) I could record the member name in the file that the trigger writes to. Something like "CURRENT DATE" except "CURRENT MEMBER". I have already tried making a trigger against an Alias which points to the appropriate member with no luck and "CURRENT MEMBER" just and error stating "Token MEMBER was not valide." I'm using DB2 on an iSeries, V6R1. Any thoughts?
Updated on 2012-11-30T20:26:32Z at 2012-11-30T20:26:32Z by SystemAdmin
  • krmilligan
    krmilligan
    446 Posts
    ACCEPTED ANSWER

    Re: Is there a way in SQL to get the current member of a table?

    ‏2012-11-26T19:51:56Z  in response to SystemAdmin
    I'm not aware of any SQL constructs that would return current member info. Is there a field value that could be examined to determine what member a record is stored in?
    • SystemAdmin
      SystemAdmin
      3129 Posts
      ACCEPTED ANSWER

      Re: Is there a way in SQL to get the current member of a table?

      ‏2012-11-26T21:47:03Z  in response to krmilligan
      Unfortunately no. The data looks the same in any of the MEMBERs, it just means something different depending on which MEMBER it is in.
  • SystemAdmin
    SystemAdmin
    3129 Posts
    ACCEPTED ANSWER

    Re: Is there a way in SQL to get the current member of a table?

    ‏2012-11-30T20:26:32Z  in response to SystemAdmin
    Apparently I was looking at the DB2 documentation for z/OS, the iSeries version of DB2 does not support CURRENT MEMBER. Also TRIGGERs are a file level attribute and therefor cannot act upon a specific MEMBER. The answer seems to be writing a CL or RPG program to handle the INSERT into the transaction file since a program can get to the MEMBER information. The Trigger will still fire regardless of MEMBER but at least this way I can avoid thousands of unnecessary rows being added to the transaction table.