CODEWITHSUNDEEP
postgresqltriggers
user3770062
user3770062

Reputation: 323

Updating multiple records in multiple tables using trigger

I am trying to write a trigger function in postgres that will update multiple records in multiple other tables when a record to a table is added.

For example I have the schema 'survey' and a table called 'a_household'.

Within this schema there are multiple tables that have a field 'hh_id' that reference the id of the household table.

The hh_id of all these tables only references the id of the household table if other attributes of the tables match.

Currently my failed coding looks like:

DECLARE
  tables text[] = ARRAY['b_member','f_firewood'];
  table_name text;
  r record;
BEGIN
  FOREACH table_name IN ARRAY tables
  LOOP        
    INSERT INTO survey.table_name(hh_id) values (NEW.id)
    SELECT * FROM survey.table_name
    WHERE survey.table_name.a= NEW.b; 
  END LOOP;
END;

I am failing completely - any advice appreciated.

My second attempt (after reading the response below) was:

DECLARE
  tables text[] = ARRAY['b_member'];
  table_name text; 

BEGIN

if tg_op='INSERT' then

FOREACH table_name IN ARRAY tables
  LOOP      
  EXECUTE 'UPDATE ' || 'survey.' || table_name || ' SET hh_id = '||NEW.id||' FROM household.a_household WHERE (select survey.b_member.odk_parentkey from survey.b_member
      where survey.b_member.odk_key = "'||NEW.odk_key||'");';

  END LOOP;

end if;

return new;

END;

but I am returned the error message 'column 'hgd' does not exist. This is the value of NEW.odk_key Becky

Upvotes: 0

Views: 345

Answers (1)

Houari
Houari

Reputation: 5621

CREATE OR REPLACE FUNCTION survey.insert_in_other_tables()
  RETURNS trigger AS
$BODY$
declare tables text[] = ARRAY['b_member','f_firewood'];
table_name text; 

BEGIN

if tg_op='INSERT' then

FOREACH table_name IN ARRAY tables
  LOOP      
  EXECUTE 'INSERT INTO ' || 'survey.' || table_name || '(hh_id) values ('||NEW.id||');';
  END LOOP;

end if;

return new;

end;$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Upvotes: 1

Related Questions