I need a function which will insert rows in multiple tables

Question

postgresql

So i am inserting a row in a table1 and this row contains one field which is a field in table2. So i would like to create a function which will insert a row in table2 when i am inserting a row in table1. So example: I have two tables

 table1
 ....
 ....
 .... 

table2
....
....
....

I insert in table1 Insert in table1 values ("Sam","USA"); as a result i want to have

table1
Sam Usa
...
...
...

table2
Usa ...
...
...

So what function should i write and what trigger? Also if there is a row in table2 which has a field USA, this function should not insert one more row with USA

Sorry, if i explained it to complicated

I think that this is a probably XYProblem That's because you are trying to do something that is a bad practice here, duplicating your data. If every value that you try to insert in table1 for that field should be in table2 then the right way would be to have a foreign key from table2 in table1. Read the provided link and then explain better your problem so we can give you a really good solution rather then a solution for a bad design. — Jorge Campos

Alex Lyalka Alex Lyalka · Accepted Answer · 2017-05-22T22:31:57

Like @Jorge Campos mentioned in comment duplication of data is usually bad idea.

But if you are really have some scenario where you need to do this you need to create trigger on source table and insert data in destination table with existing check. Here is example in SQL Server:

CREATE TABLE Tbl1(
    Id INT NOT NULL IDENTITY PRIMARY KEY,
    Name NVARCHAR(100) NOT NULL 
)
GO

CREATE TABLE Tbl2(
    Id INT NOT NULL IDENTITY PRIMARY KEY,
    Name NVARCHAR(100) NOT NULL 
)
GO

CREATE TRIGGER Sync ON Tbl1 AFTER INSERT AS 

    INSERT INTO Tbl2 (Name)
    SELECT src.Name FROM inserted src
    LEFT JOIN Tbl2 dst ON src.Name = dst.Name 
    WHERE dst.Id IS NULL
GO

INSERT INTO Tbl1 (Name) VALUES ('STR')

SELECT * FROM Tbl2

I need a function which will insert rows in multiple tables

1 Answers