2
create table tab1(id int,name text);
create table tab2(id int,name text);

  create or replace function t_tab() returns trigger
as
$$
begin
insert into tab2
values (new.id, new.name)
return new;     
end;
$$
language plpgsql;

create trigger t_tab
after insert on tab1
for each row
execute procedure t_tab();

The statement:

insert into tab1(1,'king');

inserts data in both tables tab1 and tab2.

Now my question is: I want same thing in tab2 table. When I insert data in tab2 table automatically insert same data in tab1. If I create same trigger function in tab2 then the execution unlimited. Is there any solution to fix this?

closed as off-topic by Philᵀᴹ, hot2use, Marco, Mr.Brownstone, Max Vernon Aug 21 '18 at 18:14

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Philᵀᴹ, Marco, Mr.Brownstone, Max Vernon
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 2
    Why don't you use a single table? – a_horse_with_no_name Aug 13 '18 at 6:16
  • because tab1 and tab2 access two different user – Mojib Aug 13 '18 at 6:43
  • 1
    Then use views to give access to a subset of the table, or row level security – a_horse_with_no_name Aug 13 '18 at 7:37
  • 5
    Misguided. Why don't you ask a question on the site detailing the need/requirement for separate tables, and we can give you advice? – Philᵀᴹ Aug 13 '18 at 8:15
1

assuming ID is a primary key.

on conflict do nothing:

create or replace function t2_tab() returns trigger
as
$$declare r record;
begin
insert into tab2                                 
select new.*
on conflict do nothing
returning * into r;
return r;    
end;                
$$ language plpgsql;

Not the answer you're looking for? Browse other questions tagged or ask your own question.