Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2007
    Posts
    2

    Unanswered: Trigger Invocation Issue

    Hello,

    I have coded the following trigger for an ACCPAC ERP 5.4a database based on a Persvasive SQL 9.5 back-end:

    CREATE TRIGGER UPDATE_STDCOST_FOR_ALL_ITEMS
    AFTER UPDATE ON ICILOC
    FOR EACH ROW
    IF OLD.STDCOST <> NEW.STDCOST
    AND ITEMS_USING_COMPONENT(NEW.ITEMNO) > 0 THEN

    CALL CASCADE_STDCOST_FIELD_UPDATE (NEW.ITEMNO,
    OLD.STDCOST, NEW.STDCOST);
    END IF;

    The stored procedure referenced in the trigger is as follows:

    CREATE TRIGGER UPDATE_STDCOST_FOR_ALL_ITEMS
    AFTER UPDATE ON ICILOC
    FOR EACH ROW
    IF OLD.STDCOST <> NEW.STDCOST
    AND ITEMS_USING_COMPONENT(NEW.ITEMNO) > 0 THEN

    CALL CASCADE_STDCOST_FIELD_UPDATE (NEW.ITEMNO,
    OLD.STDCOST, NEW.STDCOST);
    END IF;

    The user-defined function referenced above goes like:

    CREATE FUNCTION ITEMS_USING_COMPONENT(:COMPONENT_ITEM_NO char(24))
    RETURNS integer AS
    BEGIN
    DECLARE :END_RESULT integer;
    SELECT COUNT(*) INTO :END_RESULT FROM ICBOMD
    WHERE COMPONENT = :COMPONENT_ITEM_NO AND RTrim(LTrim(BOMNO)) = '1';
    RETURN :END_RESULT;
    END;

    And finally, here's the code for the FETCH_QTY function:

    CREATE FUNCTION FETCH_QTY(:ITEM_NO char(24),
    :COMPONENT_ITEM_NO char(24))
    RETURNS decimal(19,4) AS
    BEGIN
    DECLARE :QTY_REQUIRED decimal(19,4);
    SELECT QTY INTO :QTY_REQUIRED FROM ICBOMD WHERE ITEMNO = :ITEM_NO
    AND COMPONENT = :COMPONENT_ITEM_NO
    AND RTrim(LTrim(BOMNO)) = '1';
    RETURN :QTY_REQUIRED;
    END;

    The trigger does produce the right results when I update the STDCOST field directly from the control center. But it does not get fired when I update the standard cost through the ACCPAC ERP 5.4a interface. If you have any ideas why this happens or can happen, please share them. Thank you.

  2. #2
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    Triggers and Btrieve applications do not mix.
    In fact, in most cases Btrieve access is disabled when triggers are created. For example, if you have an Update trigger then Btrieve Updates are disabled and would return a status 149. Even if it didn't return the status 149, Btrieve cannot cause triggers to fire.
    There's no way around it.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  3. #3
    Join Date
    Nov 2007
    Posts
    2

    The future of triggers in Pervasive

    Does this then mean that there is no use of implementing triggers in applications that talk to a Pervasive database? Our clients desparately require functionality that involves making updates that are due as a result of other updates. They use off-the-shelf software. Is there just no way we can do this?

  4. #4
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    That's right. THere's really no use implementing triggers.
    I'm not aware of any way to do this.
    Sorry.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •