Cascading update triggers

• floris-1084478

  SSC Rookie

  Points: 30

  More actions

  May 6, 2010 at 8:52 am

  #146923

  Hi All,

  I have a question about triggers and cascade updates.

  Litte background info:

  2 Tables -> tblProduct & tblSaleLine (there are other tables, but not important for this question).

  Both tables contain a field called: EAN (varchar(10)).

  For the tblProduct table EAN is the Primary key, and tblSaleLine has a ForeignKey relation to tblProduct with Cascade update enabled.

  tblSaleLine has an AFTER UPDATE triggers which does some logic with the stock of the product (EAN). The logic of the trigger is that it will always undo the old situation before it executes the new code (with the updated values).

  My problem now is:

  When I update the EAN value of a product in the tblProduct table, this will cause a cascade update to all its child tables. When the tblSaleLine table rows are updated, this causes the AFTER UPDATE trigger to fire on the tblSaleLine. But....because the old and new value for EAN is actually the same (but just updated), this is wat happens:

  - All sales for that product are added to the old EAN value (unde the old situation)

  - All sales for that product are then substracted from the original stock.

  This is a good thing if the EAN value of a saleline actually changes (say: the customer want another product)...but in this situation it is not the desired outcome.

  My Question:

  Is there a way to check if the update (caller?) is a cascade update? Or can i define on a trigger to NOT fire when the update is from a Cascade action.

  We are really stuck with this..and the customer want answers

  If anyone knows a solution...i would be very happy!

  Cheers,

  Floris