update tables data dynamically

Can u help me in creating a trigger and function so that every time i change the flag in table A the buffer distance should get changed and also its geometry field also , ie the polygon area?

I have two tables named A and B.

Table A has the fields GID (primary key), FLAG, Buffer_distance, and the geometry field. Table B has the fields GID and buffer_distance, and has only three records as shown below:

GID(PK) Buffer_distance(Double Precision) 1 1000 2 2000 3 3000 

Table A's structure:

gid(PK) buffer_distance(Double Precision) flag(Text) the_geom( Geometry) 1 200 1 the_geom 2 100 2 the_geom 3 100 3 the_geom 4 500 3 the_geom 5 300 2 the_geom 6 899 1 the_geom 

What I want is the value of flag in table A to be updated from table B, and as soon as I updated the value of flag in table A the buffer_distance in A should also get replaced along with the geometry.

If what you want is to update buffer_distance in Table A based on the flag value (ie, buffer_distance=2000 when flag=2), can anyone write me an SQL case statement that will accomplish this. along with this the_geom Filed the geometry should also change with the new buffer_distance. i used the following code. help me to correct the same

create or replace function update_point_buffer() returns trigger as $$ begin -- delete IF (TG_OP = 'UPDATE') THEN UPDATE tablea SET buffer_distance = (SELECT tableb.buffer_distance FROM tableb WHERE "tableb.gid" = "tablea.flag"); END IF; return NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER trg_upgradeBufferTrigger AFTER UPDATE ON tablea FOR EACH ROW EXECUTE PROCEDURE update_point_buffer(); 

i have used this query bit it has given this error ERROR: syntax error at or near "v_output" LINE 2: v_output integer;

create or replace function update_point_buffer(v_pointflag) returns number as---------------------Return buffer distance v_output number ; begin

SELECT buffer_distance into v_output FROM point_buffer WHERE gid = v_pointflag;

return v_output; END;

CREATE TRIGGER trg_upgradeBufferTrigger-----------------------------------Update table from B to A AFTER UPDATE ON point FOR EACH ROW

begin

update point_buffer set buffer_distance= (select buffer_distance from table B where gis= :new.gis)

where gis= :new.gis

end;

can any body correct it for use.