Re: Trigger on Postgres for tables syncronization

On Tue, 27 Jul 2004, Prabu Subroto wrote:

> But I think, the modification of records to the table
> "appointment0" dan "appointment1" must be done
> automatically if my program modifies the
> "appointment". That's why I think I should use trigger
> and function.

Views would show the changes immediately.  The only issue would be if you
wanted to also allow insert/update/delete to appointment0 and appointment1
directly which would require writing correct rules for those cases.

create view appointment0 as
 select * from appointment where done='Y';
create view appointment1 as
 select * from appointment where done='N';

-----
However, you can also do this with a trigger, but that means you're
storing the data multiple times.  An untested (probably buggy) example for
insert:

create or replace function appointmentins() returns trigger AS '
begin
 if NEW.done=''Y'' then
  insert into appointment0 (noapp, custid, salesid, date, time, todo,
   done, warned, timestamp) VALUES (NEW.noapp, NEW.custid, NEW.salesid,
   NEW.date, NEW.time, NEW.todo, NEW.done, NEW.warned, NEW.timestamp);
 elsif NEW.done=''N'' then
  insert into appointment1 (noapp, custid, salesid, date, time, todo,
   done, warned, timestamp) VALUES (NEW.noapp, NEW.custid, NEW.salesid,
   NEW.date, NEW.time, NEW.todo, NEW.done, NEW.warned, NEW.timestamp);
 else
  -- what to do here? Is there a constraint that makes this impossible?
 end if;
 return NEW;
end;' language 'plpgsql';
create trigger appointmentinstrig after insert on appointment for each
row execute procedure appointmentins();

Delete is similar to the above.  Update is a little harder because you may
need to move rows from one subset to the other.