Trigger to update a table when data in other table is updated

140 views Asked by At

I have a scenario where my table(Table_A) is getting the data replicated from another table(Table_B) which is on another server.

I want to write a trigger on Table A such that it inserts a value/row in a third table(Table_C) which is on the same server as Table_A.

I tried a trigger like this:

Function:

create or replace function drs_function1() returns trigger as $$
begin

if(TG_OP = 'INSERT') then
insert into Table_C values (NEW.loginname,'NEW');
return NEW;
elsif (TG_OP = 'DELETE') then
insert into Table_C values (OLD.loginname,'DELETED');
return OLD;
elsif(TG_OP = 'UPDATE') then
insert into Table_C values (NEW.loginname,'NEW');
return NEW;
end if;
end;
$$ language plpgsql;

Trigger:

create trigger trigger5 after insert or update or delete on Table_A for each row execute procedure drs_function1();

This works fine if I manually fire a insert/update/delete query on Table_A but the trigger does not fire when the data is fed through the replication from Table_B.

The system syncs Table_B with Table_A every 5min .

So, if we change data in Table_B the sync will put this data in Table_A and the trigger should fire at that point to insert data in Table_C.

Any help would be appreciated?

Thanks.

0

There are 0 answers