Tweet
I would like to create a SQL trigger on a DB2 file (AS/400 V5R3). I'm looking for some examples, specifically an update trigger on a single field (optcode) that would check the same file and update the optcode on any other records that have the same email address as the record that triggered the update. I'm new to SQL, and am not sure how to code 'Where email_address = email_address'. Also, could this cause a recursive update that would be stuck in a loop? Thanks. DEBIEG
-
-
Creating SQL Trigger that updates other rows of same file
Debieg, A Trigger is invoked by the Data base engine and can be fired by one of 6 events: Before: Add, Update or Delete After: Add, Update or Delete You decide which event when you create the Trigger. A Table can have a Trigger for any or All of these 6 events. The value of a column is irrelevant to whether or not the Trigger is invoked. You would need to check the value of the column within the Trigger Program and condition your logic accordingly. An After Update Trigger has access to both the Old and New values of the Columns in your Table, so if you needed to check for a change, you can condition your code on: Old.optcode <> New.Optcode. Your example does look like it would cause a problem with recursions. In addition to a SQL Trigger, you can also create Trigger Programs using any HLL and specify the program on the ADDPFTRG command. When creating a Trigger program using an HLL it must accept a pre-defined set of parameters. Using the two links below you can navigate to some examples. One Link discusses SQL Triggers and the other discusses HLL Trigger programs. SQL Trigger http://publib.boulder.ibm.com/iserie...2924/index.htm Trigger Pgms http://publib.boulder.ibm.com/iserie...2924/index.htm Mike
Comment