Re: [HACKERS] Firing trigger if only

[Gabriel]

Thank you, this done the job.All the best 



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Firing-trigger-if-only-tp5798484p5799344.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.


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

Re: [HACKERS] Firing trigger if only

[Fabrízio de Royes Mello]

On Thu, Apr 3, 2014 at 8:50 AM, Gabriel yu1...@gmail.com wrote:

 Good afternoon all.I have some problem with triggers on PostgreSQL 8.4.I
 have
 a trigger on specific table(articles) that fires on update statement:

 CREATE OR REPLACE FUNCTION trigger_articles_update()
   RETURNS trigger AS
 $BODY$BEGIN
   INSERT INTO
 article_change(article_id,change_type)VALUES(OLD.article_id,2);
RETURN NULL;
 END$BODY$
   LANGUAGE 'plpgsql' VOLATILE
   COST 100;
 ALTER FUNCTION trigger_articles_update() OWNER TO postgres;

 I have 2 different applications that performs update on table
 articles(written in Delphi and using ZeosDB). My problem is that I want
 trigger to fire only when performing update with first application, but not
 with second.I know that triggers supposed to fire on every change on table,
 but this is a specific problem that I have.Any hint appreciated. 8)


Since 9.0 version of PostgreSQL you can set 'application_name' in
connection [1] and test it in your trigger using a query like:

regress=# SELECT application_name FROM pg_stat_activity WHERE pid =
pg_backend_pid();
 application_name
--
 psql
(1 registro)

I strongly recommend you to upgrade your 8.4 because the EOL [2] is July
2014.

But if an upgrade isn't an option for now then you can use the old
custom_variable_classes to set your application, i.e.:

1) Add to your postgresql.conf:

custom_variable_classes = 'foo'
foo.application_name = 'undefined'

2) Reload your PostgreSQL

3) You can use the following functions to get/set the
'foo.application_name' custom variable:

-- get
SELECT current_setting('foo.application_name');

-- set
SELECT set_config('foo.application_name', 'myapp');

4) Now you can use this functions do register the name of your application
an use it in your trigger.

Regards,

[1]
http://www.postgresql.org/docs/current/static/runtime-config-logging.html#GUC-APPLICATION-NAME
[2] http://www.postgresql.org/support/versioning/
[3] http://www.postgresql.org/docs/8.4/static/runtime-config-custom.html
-- 
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
 Timbira: http://www.timbira.com.br
 Blog sobre TI: http://fabriziomello.blogspot.com
 Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
 Twitter: http://twitter.com/fabriziomello

[HACKERS] Firing trigger if only

[Gabriel]

Good afternoon all.I have some problem with triggers on PostgreSQL 8.4.I have
a trigger on specific table(articles) that fires on update statement:

CREATE OR REPLACE FUNCTION trigger_articles_update()
  RETURNS trigger AS
$BODY$BEGIN 
  INSERT INTO
article_change(article_id,change_type)VALUES(OLD.article_id,2);
   RETURN NULL; 
END$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION trigger_articles_update() OWNER TO postgres;

I have 2 different applications that performs update on table
articles(written in Delphi and using ZeosDB). My problem is that I want
trigger to fire only when performing update with first application, but not
with second.I know that triggers supposed to fire on every change on table,
but this is a specific problem that I have.Any hint appreciated. 8)



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Firing-trigger-if-only-tp5798484.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.


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