xTuple.com xTupleU Blog & News Customer Support

Ensure trigger execution of custom trigger

I tried to add a trigger to the charass table

CREATE TRIGGER charassheattrigger
BEFORE INSERT OR UPDATE
ON public.charass
FOR EACH ROW
EXECUTE PROCEDURE public._charassheattrigger();

With the function doing

CREATE OR REPLACE FUNCTION public._charassheattrigger()
RETURNS trigger
LANGUAGE ‘plpgsql’
COST 100
VOLATILE
AS $BODY$
BEGIN
IF (TG_OP = ‘INSERT’ OR (TG_OP = ‘UPDATE’ AND OLD.charass_value <> NEW.charass_value)) AND NEW.charass_char_id = 34 THEN
IF NOT EXISTS(SELECT true FROM pittsteelcustom.heat WHERE heat_number = NEW.charass_value) THEN
INSERT INTO pittsteelcustom.heat (heat_number,heat_created,heat_vend_id)
SELECT NEW.charass_value,NOW(), pohead_vend_id
FROM ls
JOIN invdetail ON (ls_id = invdetail_ls_id)
JOIN invhist ON (invdetail_invhist_id = invhist_id)
JOIN pohead ON (invhist_ordhead_id = pohead_id)
WHERE invhist_ordtype = ‘PO’ AND invhist_transtype = ‘RP’ AND ls_id = NEW.charass_target_id;
END IF;
END IF;
RETURN NEW;
END;
$BODY$;
ALTER FUNCTION public._charassheattrigger()
OWNER TO xtrole;

If I enter a receipt I expect this to add a entry to my custom table “heat”. I can see in the log that the client executes the statement.

statement: INSERT INTO charass (charass_value, charass_target_type, charass_target_id, charass_char_id) VALUES (‘testheatnumber’, ‘LS’, 26492, 34)

But If i check the Database for my heat table. Nothing is there. If i copy and paste the exact same SQL statement into a tool like PGAdmin and execute it, Then the record appears in the heat table.

Is there anything telling the statement from the client to ignore triggers?

Nothing looks inherently wrong but there are a couple of things you might want to consider.

An update of a table is encapsulated in a transaction so any errors will cause the entire transaction including trigger functions to roll back.

You have chosen to run your trigger BEFORE the charass table has been updated. I’m looking at the join that links the lot/serial (ls_id) to the charass_target_id. I suspect you might be having a timing issue with the data.

Generally you would use a BEFORE trigger to validate data before it is written tot he table. I would use an AFTER trigger to update data in other tables after the original table has been populated.

Give that a try and let us know if it resolves the situation.

@anderson thanks for the response. I was hopeful that changing the trigger to after would fix my problem but it did not.

some addition information would be that if I take out the sql query to find the vendor then the problem goes away

So i changed all of my joins to left join and I coalesce(pohead_vend_id,-1) Still have no update. The only thing that I can think of is that the LS record ins’t created till later. I don’t see the creation of the LS record anywhere in the log, which I think means that it is wrapped into a function

I guess it could be possible that the LS record is not created until after the characteristics are posted. It would seem a little strange but I could not confirm without digging in to the source code. There is some funky transactional processes in that area of the code in order to facilitate rollback is errors occur.

One thing you could try is to reverse the logic. Create your trigger function off the LS table. From what I can see all your logic is hanging of the ls_id anyway. From the AFTER INSERT UPDATE of ls lookup the charass table and then populate your heat table

I set up a backup and used auto_explain. Which give me a mess that I believe tells me that. LS created —> Charass —> invhist.

Im going to move my trigger over to that table and check for the ‘RP’ type. Ill let you know if that works out better

After moving the trigger to the invhist table I get the behavior I expect