By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,591 Members | 650 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,591 IT Pros & Developers. It's quick & easy.

trigger to distribute the data in other tables (by conditions)

P: 7
Here again with other trigger:

the tables structure of the database:
"database_t" (master),
"azienda_vallone" (SLAVE1),
"azienda_energy" (SLAVE2 )
....
all tables have identical structure.

I want to create a trigger that on updates of table "database_t" I update the fields in the table "azienda_vallone" and on insert in table "database_t" I insert rows in the table "azienda_vallone" on one condition: If on updated or inserted row the column intestatario_pratica = 'AZ. AGR.LA VALLONE' the line goes in table "azienda_vallone" or if = 'AZ. AGR.LA ENERGY' the line goes "azienda_energy" (this second part not yet implemented in trigger).

Follows my attempt:

Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE FUNCTION trig_agg_tab_aziende()
  2.   RETURNS trigger AS
  3. $BODY$
  4.     BEGIN
  5.         IF (TG_OP = 'UPDATE') THEN
  6.     UPDATE azienda_vallone SET (sigla_comune, foglio, mappale, livello, the_geom, proprieta, 
  7.        denominazione, titolo_disponibilita, titolo_possesso, sigla_provincia, 
  8.        provincia, comune, porzione, sup_catastale, qualita_catastale, 
  9.        vulnerabilita, sup_rilevata, sup_divieto, note_divieto, sup_spandibile, 
  10.        coltura_pac, nome_suolo, num_suolo, fascia_altimetrica, area_omogenea, 
  11.        appezzamento, codice_pua, particella_soppressa, superficie_calcolata, 
  12.        zona_prg, articolo_prg, coltura_pan_09, coltura_pan_10, coltura_pan_11, intestatario_pratica, nome_tabella) = (
  13.        NEW.sigla_comune, NEW.foglio, NEW.mappale, NEW.livello, NEW.the_geom, NEW.proprieta, 
  14.        NEW.denominazione, NEW.titolo_disponibilita, NEW.titolo_possesso, NEW.sigla_provincia, 
  15.        NEW.provincia, NEW.comune, NEW.porzione, NEW.sup_catastale, NEW.qualita_catastale, 
  16.        NEW.vulnerabilita, NEW.sup_rilevata, NEW.sup_divieto, NEW.note_divieto, NEW.sup_spandibile, 
  17.        NEW.coltura_pac, NEW.nome_suolo, NEW.num_suolo, NEW.fascia_altimetrica, NEW.area_omogenea, 
  18.        NEW.appezzamento, NEW.codice_pua, NEW.particella_soppressa, NEW.superficie_calcolata, 
  19.        NEW.zona_prg, NEW.articolo_prg, NEW.coltura_pan_09, NEW.coltura_pan_10, NEW.coltura_pan_11, NEW.intestatario_pratica, 
  20.        NEW.nome_tabella)
  21.     FROM database_t
  22.     WHERE NEW.intestatario_pratica = 'AZ. AGR.LA VALLONE';
  23.         ELSIF (TG_OP = 'INSERT') THEN
  24.     INSERT INTO azienda_vallone SELECT 
  25.        NEW.sigla_comune, NEW.foglio, NEW.mappale, NEW.livello, NEW.the_geom, NEW.proprieta, 
  26.        NEW.denominazione, NEW.titolo_disponibilita, NEW.titolo_possesso, NEW.sigla_provincia, 
  27.        NEW.provincia, NEW.comune, NEW.porzione, NEW.sup_catastale, NEW.qualita_catastale, 
  28.        NEW.vulnerabilita, NEW.sup_rilevata, NEW.sup_divieto, NEW.note_divieto, NEW.sup_spandibile, 
  29.        NEW.coltura_pac, NEW.nome_suolo, NEW.num_suolo, NEW.fascia_altimetrica, NEW.area_omogenea, 
  30.        NEW.appezzamento, NEW.codice_pua, NEW.particella_soppressa, NEW.superficie_calcolata, 
  31.        NEW.zona_prg, NEW.articolo_prg, NEW.coltura_pan_09, NEW.coltura_pan_10, NEW.coltura_pan_11, NEW.intestatario_pratica, 
  32.        NEW.nome_tabella
  33.     FROM database_t
  34.     WHERE NEW.intestatario_pratica = 'AZ. AGR.LA VALLONE';
  35.         END IF;
  36.     RETURN NEW;
  37.     END;
  38. $BODY$
  39.   LANGUAGE 'plpgsql' VOLATILE
  40.   COST 100;
  41. ALTER FUNCTION trig_agg_tab_aziende() OWNER TO postgres;
that function return no error but on insert or update nothing happens.

thanks to all who help me!!
Sentenza
(I hope I have properly tagged the code)
May 18 '10 #1
Share this Question
Share on Google+
5 Replies


Expert 100+
P: 700
And where is trigger definition? You didn't put it here.
May 18 '10 #2

P: 7
Thanks for the answer rski

here the definition:

Expand|Select|Wrap|Line Numbers
  1. CREATE TRIGGER agg_tab_aziende 
  2. AFTER INSERT OR UPDATE 
  3. ON database_t
  4. FOR EACH ROW EXECUTE PROCEDURE trig_agg_tab_aziende();
last night I tried again with the table "azienda_vallone" partially populated and does not work correctly. if there are 5 rows of data and I make a change in "database_t", the trigger overwrites all 5 lines of "azienda_vallone" with the same line changed in "database_t".

I therefore have missed the basic concept ... but I do not understand!

Thanks again
sentenza
May 19 '10 #3

Expert 100+
P: 700
Why do you use
UPDATE .... FROM t_database
and
INSERT ... FROM t_database
You only should update NEW values in table azienda_vallone
or insert NEW values into that table.
NEW's are not column names but column values.
May 19 '10 #4

P: 7
so i must correct the trigger deleting:

"FROM database_t" ?

and delete NEW.* from:

WHERE NEW.intestatario_pratica = 'AZ. AGR.LA VALLONE'

then i must use:

WHERE intestatario_pratica = 'AZ. AGR.LA VALLONE'; ?


I'll try tonight.

thank you very much
Sentenza
May 20 '10 #5

P: 7
hi there
Yesterday I tried, but does not work. when running the update, it write the updated row in all rows of the table azienda_vallone, entirely overwrite all existing rows.
the guide says to mention in UPDATE only the columns that are modified but in this case is impossible to know first what columns will be modified.

Sentenza
May 21 '10 #6

Post your reply

Sign in to post your reply or Sign up for a free account.