i've been stuck on a problem and i'm hoping that someone out there can
help me out. my problem deals with three tables and one sequence. here
are some example tables
create table mom (
mom_id serial primary key
);
create table dad (
dad_id serial primary key
);
create table mom_dad(
mom_id integer referenced from mom.mom_id
dad_id integer referenced from dad.dad_id
);
a trigger on mom sets off a function that inserts any relevent
information into dad(my real tables obviously have more columns). my
problem occurs when i try to put another trigger on dad that calls a
function that inserts the id's of both tables into mom_dad. my function
looks like this;
create function parents() returns opaque as '
declare
m mom%ROWTYPE;
d dad%ROWTYPE;
md mom_dad%ROWTYPE;
mom_mom_id_seq%ROWTYPE;
d_id integer;
m_id integer;
begin
d_id := new.d_id;
insert into mom_dad(m_id, d_id) values (currval('mom_mom_id_seq'), d_id);
return null;
end;
'language 'plpgsql';
create trigger family after insert or update on dad
for each row execute procedure parents();
when i run this i get a parse error at or near "assets_asset_id_seq". i have also tried make a variable to represent
thecurrval by using a select into statement, but couldn't get it to work. when i put a raise notice statement in the
functionand manually assign a value to m_id, it returns that value. otherwise, i do not know how to return the correct
value(currvalof m_id). i have looked at all docs on sequences and have not found an example that works correctly. i
mustbe missing something. if anyone can help me out or point me in the right direction i would greatly appreicate it.
thanks in advance,
mikez
--
Mike Z
Intern
__________________________________________________
I.D.E.A.L. Technology Corporation - Orlando Office
http://www.idealcorp.com - 407.999.9870 x14
