How to make trigger on two tables?

Question

I have a two tables which insert using jdbc. For example its parcelsTable and filesTableAnd i have some cases:
1. INSERT new row in both tables.
2. INSERT new row only in parcelsTable.

TABLES:

DROP parcelsTable;
CREATE TABLE(
 num serial PRIMARY KEY,
 parcel_name text,
 filestock_id integer
)


DROP filesTable;
CREATE TABLE(
 num serial PRIMARY KEY,
 file_name text,
 files bytea
)

I want to set parcelsTable.filestock_id=filesTable.num when i have INSERT in both tables using TRIGGER.
Its possible? How to know that i insert in both tables?

Answer 1

This might not be an answer, but it may be what you need. I am making this an answer instead of a comment because I need the space.

I don't know if you can have a trigger on two tables. Typically this is not needed. As in your case, typically either you are creating a parent record and a child record, or you are just creating a child record of an existing record.

So, typically, if you need a trigger when creating both, it is sufficient to put the trigger on the parent record.

I don't think you can do what you need. What you are trying to do is populate the foreign key with the parent record primary key in the same transaction. I don't think you can do that. I think you will have to provide the foreign key in the insert for parcelsTable.

You will end up leaving this NULL when you are creating a record in the parcelsTable at times when you are not creating a record in filesTable. So I think you will want to set the foreign key in the INSERT statement.

Answer 2

You don't need to use a trigger to get the foreign key value in this case. Since you have it set as serial you can access the latest value using currval. Run something like this this from your app:

insert into filesTable (file_name, files) select 'f1', 'asdf';
insert into parcelsTable (parcel_name, filestock_id) select 'p1', currval('filesTable_num_seq');

Note that this should only be used when inserting one record at a time to grab individual key values from currval. I'm calling the default sequence name of table_column_seq, which you should be able to use unless you've explicitly declared something different.

I would also recommend explicitly declaring nullability and the relationship:

CREATE TABLE parcelsTable (
  ...
  filestock_id integer NULL REFERENCES filesTable (num)
);

Here is a working demo at SqlFiddle.

Answer 3

Only idea I've got by now is that you can create function that do indirect insert to the tables. then you can have whatever condition you need, with parallel inserts too.