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(dot)g(dot)johnston(at)gmail(dot)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(dot)klaver(at)aklaver(dot)com