RM00101 Custom Trigger Conflict

  • RM00101 Custom Trigger Conflict

    Posted by David Morinello on August 21, 2023 at 10:28 am

    I have been asked to track certain changes to the Customer Master and address tables (RM00101 & RM00102). I have created an “Audit” Tracking table and a proof-of-concept trigger for RM00101.

    The After Update SQL Trigger doesn’t update data to the RM00101 table, but writes the Del and Ins records to the new custom Audit table.

    Here is the weird behavior however; when enabled the trigger I see a “Violation of PRIMARY KEY constraint ‘PKRM00101′.
    Cannot insert duplicate key in object ‘dbo.RM00101’. The duplicate key value is xxxxxx
    .”. GPPT tracing shows GP (zDP_RM00101SI) thinking an insert being attempted to RM00101. Why?

    The symptom in GP 2018 R2 is a GP pop-up “This record has been created since your attempt to create it. Changes won’t be saved.”

    David Morinello replied 5 months, 2 weeks ago 2 Members · 6 Replies
  • 6 Replies
  • Kerry Hataley

    Organizer
    August 21, 2023 at 10:58 am

    Hi David,

    I am thinking that the issue steams from the fact that triggers are NOT executed once per row, but only once per DML operation. So you need to explicit out the triggers. This means that you ALWAYS should have individual triggers per event type. Meaning a trigger of the insert, update and delete. Each need to have their own definition. This hold true when there are other triggers attached to the table. I have seen this issue with Paramount and their triggers.

    Also remember to exclude the Dex_xxx from updates, since this will fill your audit table when doing maintenance routines.

  • David Morinello

    Member
    August 23, 2023 at 8:56 am

    I have tried “After Update” and “For Update”.

    The inserts into the tracking table are not attempting to insert the Dex_Row_ID. I have attached the SQL for the audit table and for the matching trigger. What am I missing?

  • Kerry Hataley

    Organizer
    August 29, 2023 at 2:53 pm

    Looking at the code quickly, I think the issue is with the Primary Key for RM00101 an the order in which the DMLs are triggering.

    Try changing the ‘for UPDATE’ to ‘AFTER UPDATE’.

    If not I will dig in more with you…

  • David Morinello

    Member
    August 29, 2023 at 4:25 pm

    @kerryhataley

    I have tried “After Update” and “For Update”.

    If I can’t make this work, I may turn to GPPT for a solution. The trigger solution was the quickest or should have been.

  • Kerry Hataley

    Organizer
    September 1, 2023 at 11:38 am

    Good Morning @david-morinellotruckpro-com

    OK, I can replicate this, give me the weekend to try something…

    It all has to do with the fact when calling the insert & deletes from the update trigger temp tables, the insert and delete need to be in a single sql statement using a JOIN.

    I can have my solution on Monday for you…

    • David Morinello

      Member
      September 12, 2023 at 4:12 pm

      I ended up using GP Power Tools and a variation on the Customer Tracking sample project from the Winthrop GPPT samples site.

Log in to reply.

Welcome to our new site!

Here you will find a wealth of information created for people  that are on a mission to redefine business models with cloud techinologies, AI, automation, low code / no code applications, data, security & more to compete in the Acceleration Economy!