execute trigger after another one

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.

Also, you say "it seems" - can you put forth specific proof that one is
firing before the other?

David J.

--
View this message in context: http://postgresql.nabble.com/execute-trigger-after-another-one-tp5829308p5829311.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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?

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 12/4/2014 6:11 PM, Carlos Carcamo wrote:

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

2014-12-04 17:31 GMT-06:00 Adrian Klaver <adrian.klaver@aklaver.com>:

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 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"

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general