ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

SQL Trigger - Preventing cascade/recursion

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • SQL Trigger - Preventing cascade/recursion

    Hi Folks

    My objective is to "expand" or "replicate" one record entered through a conventional interactive maintenance program to many records in the same file, but I only want the trigger to occur on the manually entered record.

    The scenario is this: One trading partner has many semi-independent branches, and each branch has a separate customer number in the ERP package (BPCS). There is a corporate customer to tie them all together (so to speak), but some processes work at individual customer level only. One such is the Customer/Item X-Ref - a file in which the customer's SKU is mapped to the BPCS SKU so the customer can order using the product in their database and which they are comfortable with. There is no way to enter it against the corporate customer, which is effectively a dummy for billing (it's not a branch that orders stock).

    However all branches (customers) have the same codes. So the objective is to enter each mapped pair (their SKU = our SKU) once only and have it replicate as many times as there are customers in the trading partner group.

    Not really too difficult and I've done it, but the records I add in my trigger (let's say 30 branches) then trigger another set of 30 and so on.

    So, what I want to do is somehow prevent the records added by the trigger triggering itself. I've checked the documentation, and the promising 'NO CASCADE' apparently is only there for compatibility with other systems and does nothing.


  • #2
    Addendum: I was overlooking an obvious solution. All I had to do was drop something into my trigger-added records that couldn't possibly be in a user-added record (e.g. a non-existent user-profile name) and then add to the WHEN condition that the user-profile not be that value. Duh.
    Last edited by WilliamTasker; August 15, 2017, 02:39 AM.

    Comment

    Working...
    X