RE: SQL Triggers and Multiple Member Database Files

[vhamberg@xxxxxxxxxxx]

The "Stored Procedures, Triggers and User Defined Functions" Redbook mentions 
in Chapter 10. SQL Triggers, section 10.7, something about accessing triggering 
data, or the trigger buffer. It says that there is some information not 
available to SQL triggers, including trigger event, trigger time, commit 
level,and relative record number. Now it does not say member & file info is not 
available. There are things called correlation variables and transition tables 
- maybe something here can help - I've not used this much yet.

Vern
-------------- Original message -------------- 

> Charles, 
> 
> Thanks for replying. I do have to disagree to a point about one of your 
> statements. 
> 
> "Which would make sense since SQL doesn't have any concept of multiple 
> members." 
> 
> Well, yes and no. As a workaround, you can define an alias for a member 
> and reference it like a table in a SQL statement. 
> 
> In this case, what I am really looking for is a way to reference the 
> trigger buffer in SQL, which contains the member name. Based on what I 
> have seen so far, I think you may be correct, Charles. 
> 
> Thanks, 
> 
> Jim 
> 
> -----Original Message----- 
> From: midrange-l-bounces@xxxxxxxxxxxx 
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Wilt, Charles 
> Sent: Tuesday, April 05, 2005 9:57 AM 
> To: Midrange Systems Technical Discussion 
> Subject: RE: SQL Triggers and Multiple Member Database Files 
> 
> As far as I know, it can't be done with an SQL trigger. 
> 
> Which would make sense since SQL doesn't have any concept of multiple 
> members. 
> 
> You have two options: 
> 1) Change the program to use a temporary work file. 
> 2) Use an RPG/COBOL/C program for your trigger. 
> 
> HTH, 
> 
> 
> Charles Wilt 
> iSeries Systems Administrator / Developer Mitsubishi Electric Automotive 
> America 
> ph: 513-573-4343 
> fax: 513-398-1121 
> 
> 
> > -----Original Message----- 
> > From: midrange-l-bounces@xxxxxxxxxxxx 
> > [mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Reinardy, James 
> > Sent: Tuesday, April 05, 2005 10:09 AM 
> > To: Midrange Systems Technical Discussion 
> > Subject: SQL Triggers and Multiple Member Database Files 
> > 
> > 
> > We are using SQL Triggers on an 830 running v5r2 in connection with an 
> 
> > application that is writing transactions to an audit file for later 
> > updates to a data warehouse. We are finding that one source of a lot 
> > of transactions is a program that creates and then deletes a temporary 
> 
> > member in one of our major database files. These transactions are 
> > useless to us, and are causing some issues because the key values are 
> > the same as the primary member, and the audit records show up as 
> > another insert of the same keys. I am looking for a way to reference 
> > the member name in the SQL trigger to exclude updates to the secondary 
> 
> > member from being written to our transaction audit files. Any ideas 
> > about how to do this? 
> > 
> > Thanks, 
> > 
> > Jim Reinardy 
> > Badger Meter, Inc. 
> > -- 
> > This is the Midrange Systems Technical Discussion 
> > (MIDRANGE-L) mailing list 
> > To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, 
> > unsubscribe, or change list options, 
> > visit: http://lists.midrange.com/mailman/listinfo/midrange-l 
> > or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take 
> > a moment to review the archives at 
> > http://archive.midrange.com/midrange-l. 
> > 
> > 
> 
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing 
> list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, 
> unsubscribe, or change list options, 
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l 
> or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a 
> moment to review the archives at http://archive.midrange.com/midrange-l. 
> 
> 
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list 
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx 
> To subscribe, unsubscribe, or change list options, 
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l 
> or email: MIDRANGE-L-request@xxxxxxxxxxxx 
> Before posting, please take a moment to review the archives 
> at http://archive.midrange.com/midrange-l. 
>