Re: execute trigger after another one

2014-12-04 17:31 GMT-06:00 Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>:
> On 12/04/2014 03:11 PM, Carlos Carcamo wrote:
>>
>> 2014-12-04 16:46 GMT-06:00 David G Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>:
>>>
>>> Carlos Carcamo wrote
>>>>
>>>> I read about oGlenrder of execution of triggers, is supposed that postgres
>>>> executes triggers in alphabetical order, so I called the plpgsql
>>>> a_trigger and the second one b_trigger (as an example), but it seems
>>>> that the second one always executes first.
>>>>
>>>> Is there any way to make triggers execute in a specific order?
>>>
>>>
>>> If two triggers would otherwise fire at the same time then alphabetical
>>> order is used to break ties. But in all situations before triggers will
>>> always fire before after triggers.
>>>
>>> But since you haven't show us the exact CREATE TRIGGER statements you are
>>> using whether that is why yours are not behaving is impossible to tell.
>>
>>
>> sorry for that, here some code:
>>
>> -- Trigger #1
>> CREATE OR REPLACE FUNCTION tgfn_kardex()
>> RETURNS trigger AS
>> $BODY$
>> BEGIN
>> IF (TG_OP = 'INSERT') THEN
>> --logic here
>> END IF;
>> --more code
>> RETURN NULL;
>> END;
>> $BODY$
>> LANGUAGE plpgsql VOLATILE
>>
>> -- then
>>
>> CREATE TRIGGER tgfn_kardex
>> AFTER INSERT OR UPDATE OR DELETE
>> ON in_kardex
>> FOR EACH ROW
>> EXECUTE PROCEDURE tgfn_kardex();
>>
>>
>> -- Trigger #2
>> CREATE OR REPLACE FUNCTION update_remote()
>> RETURNS trigger AS
>> $BODY$
>> import os
>> os.system('./var/www/update_remote.sh')
>> $BODY$
>> LANGUAGE plpython3u VOLATILE
>>
>> -- then
>>
>> CREATE TRIGGER update_remote
>> AFTER INSERT OR UPDATE OR DELETE
>> ON in_kardex
>> FOR EACH ROW
>> EXECUTE PROCEDURE update_remote();
>>
>>> Also, you say "it seems" - can you put forth specific proof that one is
>>> firing before the other?
>>
>>
>> Yes because my update_remote.sh file calls a php file to update a
>> table in mysql, and it is updated after I perform another query to
>> in_kardex, so the mysql table is one query behind postgres...
>>
>> Any thoughts?
> My bet is that the query run from your shell script can't see your changes because the original transaction is still open.
--Stephen Cook

Good point, I hadn't thought about it...

> You do not say what version of Postgres you are using, but if 9.3+ then you
> might want to look at the MySQL FDW:

I'm using 9.1

> https://github.com/EnterpriseDB/mysql_fdw

--
"El desarrollo no es material es un estado de conciencia mental"