I have tables A, B, C, and D. Each row in table A must be "linked" to
rows B, C, and D. A is a sales order for a particular job to be done.
B, C, and D are budgets for specific tasks within table A.
So for each A(sales order) there are three additional budgets (B, C and
D) that need to be linked in.
What I was going to do is create a function that pulled the "last_value"
from the primary_key on table A and insert it into a reference field in
tables B, C and D. Something like below:
create function id_insert() returns int as
'
select last_value from A_id_seq;
insert into B (id_ref) values (***selected value***);
insert into C (id_ref) values (***selected value***);
insert into D (id_ref) values (***selected value***)
'
language 'sql';
create trigger id_trig after update or insert
on table A for each row execute procedure id_insert;
Does this seem even remotely correct. If so can anyone point me to a
good source to determine the syntax for such a setup? The manual
doesn't appear to be that specific. Also how would I assign a temporary
variable with the select statement so it can be used in the inserts?
My understanding that this is the only method of accomplishing this for
the most part since using a "reference" in a "create table" simple
enforces a foreign_key_check call so that an acceptable value is used
(this was implemented when the tables were created BTW.
Thanks in Advance!
