Not knowing the size of the original files, as long as they are not too big, adding an index just to get a trigger to work might be serious overkill. You would need to make that call as only you know the size and performance hits involved in this.
Just my 2 cents worth (1 cent in this economy). John Israel Senior Programmer/Analyst Dayton Superior Corporation 1125 Byers Road Miamisburg, OH 45342 -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of David Wolverton Sent: Thursday, April 21, 2011 12:45 PM To: 'U2 Users List' Subject: Re: [U2] Very Weird Trigger Behavior I would agree -- if there is an active select list, then you would get some seriously unexpected behavior doing another select unless you 'pushed' that new select to a different 'List ID' than the default '0' list -- just ensure it's a ListID that you KNOW would never be used anywhere else. Back to the 'ideal' - the index is the 'best answer' as it will be faster AND it would avoid the entire 'select' in the first place. The downside of the index may be the in the use of this code -- if every records ends up with a status of "DONE", you probably would hate to index that 'status' field since it would be a VERY large index for the "DONE" set of keys... DW -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Israel, John R. Sent: Thursday, April 21, 2011 10:10 AM To: 'U2 Users List' Subject: Re: [U2] Very Weird Trigger Behavior Just a thought would be to change your select to use the non-default active select list. For example, instead of: CMD='SELECT TRADES WITH TRADE_CODE = "':CODE_DEF_ID:'"' try CMD='SELECT TRADES WITH TRADE_CODE = "':CODE_DEF_ID:'" TO 2' If you are running in PICK flavor, change the "SELECT" to "select" to force it to run in native UniData flavor. I am not sure how this will work with the SYSTEM(11) command, but even if that does not work, there are other ways to test if anything was returned. Note that if you DO need to loop through this, you may need to change code to read from active select 2 (not the default). You might also want to do a CLEARSELECT (or CLEARSELECT 2). John Israel Senior Programmer/Analyst Dayton Superior Corporation 1125 Byers Road Miamisburg, OH 45342 -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of jonathanm Sent: Thursday, April 21, 2011 11:03 AM To: [email protected] Subject: [U2] Very Weird Trigger Behavior Hi, all. I'm new to this forum, but have been programming multivalue database apps for years. I ran across something here that really has me puzzled. I wonder if anyone has seen something like this and might be able to shed a bit of light as to what's happening. I'm running Unidata 6.0 on hpux. I have a trigger set up to run before deletions take place on a file called CODE_DEFS. The file just contains code numbers (ID) and their meaning/description. There's another file, TRADES, that contains a field, TRADE_CODE, that should have a many-to-one relationship with IDs in CODE_DEFS. That is, the content of TRADE_CODE should match a record ID in CODE_DEFS. Before a record is deleted from CODE_DEFS, I want to ensure its ID does not appear in any record in TRADES. That's where the trigger comes in. The trigger is passed the ID of the record to be deleted. This is done via parameter variable CODE_DEF_ID. The trigger program, CHECK_TRADES, has the following logic: ---------------------------------------------------------------------------- --- . . . CMD='SELECT TRADES WITH TRADE_CODE = "':CODE_DEF_ID:'"' EXECUTE CMD IF SYSTEM(11) THEN * there's a select list, so the code is still in use in TRADES. * Return 0 in EXECUTE_STATUS to disallow the deletion. EXECUTE_STATUS=0 END . . . ---------------------------------------------------------------------------- ------- The problem is when I try to delete a record from CODE_DEFS, using a TCL DELETE command, I get the following error message. '' is not a record in TRADES Of course, there are two problems with the error message. Not only has it lost the ID of the record to be deleted, it has also lost the name of the file. Notice that it is looking in the TRADES file instead of CODE_DEFS. I believe what's happening is when it EXECUTEs the CMD, the internal variables for ID and filename are being reset. To support this hunch, I changed the trigger routine so that CMD="DATE", causing it to execute the TCL DATE command. Here's the error message from that: '' is not a record in CODE_DEFS And if I make CMD="COUNT CUST", which makes the trigger deal with a completely unrelated file, I get this error message: '' is not a record in @7X% So, it appears that any EXECUTE is going to lose the ID, and if CMD deals with any file, the filename is reset also. I really need to have it perform the intended check before deletion. Any ideas? -- View this message in context: http://old.nabble.com/Very-Weird-Trigger-Behavior-tp31450305p31450305.html Sent from the U2 - Users mailing list archive at Nabble.com. _______________________________________________ U2-Users mailing list [email protected] http://listserver.u2ug.org/mailman/listinfo/u2-users _______________________________________________ U2-Users mailing list [email protected] http://listserver.u2ug.org/mailman/listinfo/u2-users _______________________________________________ U2-Users mailing list [email protected] http://listserver.u2ug.org/mailman/listinfo/u2-users _______________________________________________ U2-Users mailing list [email protected] http://listserver.u2ug.org/mailman/listinfo/u2-users
