Re: execute trigger after another one

On 12/04/2014 03:11 PM, Carlos Carcamo wrote:
> 2014-12-04 16:46 GMT-06:00 David G Johnston <david.g.johnston@gmail.com>:
>> Carlos Carcamo wrote
>>> I read about order 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?

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:

https://github.com/EnterpriseDB/mysql_fdw

>


--
Adrian Klaver
adrian.klaver@aklaver.com