Open Forum

Expand all | Collapse all

Trigger on IV30300

  • 1.  Trigger on IV30300

    TOP CONTRIBUTOR
    Posted 10 days ago
    I created a trigger to pull data from inserted records into IV30300 into another table. Everything works but the TRXQTY field. The destination table's column is defined the same way.

    Is there something about this table that is causing an issue?

    I created a couple of custom tables to test pulling a numeric column value and had no issue. I can certainly query the table. It's just when I'm trying to use inserted records from the table where I'm not getting a value from TRXQTY.

    ------------------------------
    Charles Allen
    Senior Managing Consultant
    BKD Technologies
    Houston, TX
    ------------------------------


  • 2.  RE: Trigger on IV30300

    SILVER CONTRIBUTOR
    Posted 10 days ago
    Hello Charles,
    I'd like to see your T-SQL trigger code.  Can you email it over to me?

    ------------------------------
    Anthony Merriwether
    ETL Source LLC
    www.etlsource.com
    amerriwether@etlsource.com
    ------------------------------



  • 3.  RE: Trigger on IV30300

    TOP CONTRIBUTOR
    Posted 4 days ago
    Here's the trigger.

    CREATE TRIGGER [dbo].[BKD_ModulaLines] on [dbo].[IV30300]
    FOR INSERT
    AS
    DECLARE
    @RIG_ORDINE CHAR(21),
    @RIG_ARTICOLO CHAR(31),
    @RIG_QTAR NUMERIC(19,5),
    @IMPORTED INT
    SELECT @RIG_ORDINE = ins.DOCNUMBR FROM inserted ins;
    SELECT @RIG_ARTICOLO = ins.ITEMNMBR FROM inserted ins;
    SELECT @RIG_QTAR = ins.TRXQTY FROM inserted ins;
    SELECT @IMPORTED = 0;
    INSERT INTO [dbo].[BKD_IMP_ORDINI_RIGHE_Staging]
    ([RIG_ORDINE]
    ,[RIG_ARTICOLO]
    ,[RIG_QTAR]
    ,[IMPORTED])
    VALUES
    (@RIG_ORDINE,
    @RIG_ARTICOLO,
    @RIG_QTAR,
    @IMPORTED);

    ------------------------------
    Charles Allen
    Senior Managing Consultant
    BKD Technologies
    Houston, TX
    ------------------------------



  • 4.  RE: Trigger on IV30300

    Posted 9 days ago
    Edited by Kevin Droz 8 days ago
    There could be a delay on all the data being inserted in the table, maybe you just need to tweak your trigger.

    We saw this behavior on a Scribe integration (GP and CRM) where we were inserting Inventory data.

    You also can run a SQL trace to see what is going on if its T-SQL code related.



    ------------------------------
    Kevin
    ------------------------------



  • 5.  RE: Trigger on IV30300

    TOP CONTRIBUTOR
    Posted 9 days ago
    ​You might also be running into a competing trigger or procedure.  Most triggers take priority over a stored procedure call, so your trigger may indeed be activating properly and getting overridden by part of a stored procedure.  The only way to know would be to run an SQL Trace on the transaction (hopefully on a test box to provide isolation from the noise of common use).

    ------------------------------
    Blair Christensen
    Database Administrator
    Oppenheimer Companies, Inc.
    Boise ID
    ------------------------------



  • 6.  RE: Trigger on IV30300

    TOP CONTRIBUTOR
    Posted 4 days ago
    Hi Charles,

    I haven't tested it, but have you tried adding an UPDATE trigger to the table.  Is it possible that the data is inserted into IV30300 and then the TRXQTY is updated in another UPDATE statement?

    Not that this will help your problem, but do have one comment about your trigger.  The INSERTED table contains the row(s) of data that are being added/edited.  If a bulk insert/update occurs on the table, the INSERTED table have more than one row in it.  When you SELECT @Variable.. you ​will only get one row of the data being edited/inserted.   Now, from what I've seen, GP only updates things one row at a time, but I thought you might want to know.  Here's a version of your trigger that will work with multiple rows:

    INSERT INTO BKD_IMP_ORDINI_RIGHE_Staging
    (
      RIG_ORDINE,
      RIG_ARTICOLO,
      RIG_QTAR,
      IMPORTED
    )
    SELECT  DOCNUMBR,
      ITEMNMBR,
      TRXQTY,
      0
    FROM INSERTED

    Good Luck!

    ------------------------------
    John Arnold
    Senior Software Engineer
    US Digital
    Vancouver WA
    ------------------------------



  • 7.  RE: Trigger on IV30300

    TOP CONTRIBUTOR
    Posted 2 days ago
    Thank you.

    My first version is similar to what you have but, since I was having issues, I doubted other parts.

    ------------------------------
    Charles Allen
    Senior Managing Consultant
    BKD Technologies
    Houston, TX
    ------------------------------