[khemani]

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.

[mirtheil]

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.

[khemani]

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?

[mirtheil]

That's right. THere's really no use implementing triggers.
I'm not aware of any way to do this.
Sorry.