By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,960 Members | 990 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,960 IT Pros & Developers. It's quick & easy.

Inheritance problem

100+
P: 138
hi ,
i have one parent table with 3 fields, id, name and date with no primary key.

i have inherited 6 child tables from the parent table with the same table structure of the parent.

i have created one trigger too to insert data in the child table whenever inserted in the parent table checking the date of one year. if it's between jan and feb of that year then insert into 1st child table, if it's between march and april then insert into 2nd child table and so on.and all the child table has the primary key id.

when ever i insert the same id with the same date, then it will not allow to insert in any of the child table. but when i insert same id with different date, it allows to insert into different child table and in the parent table too.

what i wanted is whenever i insert the id and the date, if the id exists in any of the child table, it should not insert into either the parent table or any child table.
is it possible to make all the child tables behave as a single table to check whether the id already exists or not???

is it possible to do so?

even if i use the primary key in the parent table i.e. id, it still inserts the data in the child tables according to the date.

plz help
with best regards
Mar 24 '08 #1
Share this Question
Share on Google+
3 Replies


Expert 100+
P: 700
hi ,
i have one parent table with 3 fields, id, name and date with no primary key.

i have inherited 6 child tables from the parent table with the same table structure of the parent.

i have created one trigger too to insert data in the child table whenever inserted in the parent table checking the date of one year. if it's between jan and feb of that year then insert into 1st child table, if it's between march and april then insert into 2nd child table and so on.and all the child table has the primary key id.

when ever i insert the same id with the same date, then it will not allow to insert in any of the child table. but when i insert same id with different date, it allows to insert into different child table and in the parent table too.

what i wanted is whenever i insert the id and the date, if the id exists in any of the child table, it should not insert into either the parent table or any child table.
is it possible to make all the child tables behave as a single table to check whether the id already exists or not???

is it possible to do so?

even if i use the primary key in the parent table i.e. id, it still inserts the data in the child tables according to the date.

plz help
with best regards
How did you write a trigger?
You can try write a trigger that checks if id exists in tables and if not found any rows in child tables with containing current inserting id and performing the insert wheen non rows found. Can you show the trigger function code?
Mar 24 '08 #2

100+
P: 138
How did you write a trigger?
You can try write a trigger that checks if id exists in tables and if not found any rows in child tables with containing current inserting id and performing the insert when non rows found. Can you show the trigger function code?
here's my code
---the master table --

Expand|Select|Wrap|Line Numbers
  1. create table tbl_mst(
  2. id varchar(20) not null,
  3. name varchar(50),
  4. entry_date date);
  5.  
  6. ----the child tables ------
  7. create tbl_child1(
  8. check(entry_date >= DATE '2008-01-01' AND entry_date < '2008-03-01')
  9. ) INHERITS (tbl_mst);
  10.  
  11. create tbl_child2(
  12. check(entry_date >= DATE '2008-03-01' AND entry_date < '2008-05-01')
  13. ) INHERITS (tbl_mst);
  14. and so on tbl_child3 to tbl_child6;
  15.  
  16. ----assigning primary key to child tables------
  17. alter table tbl_child1 add constraints pk_child1(id);
  18. alter table tbl_child1 add constraints pk_child2(id);
  19. and so on for child3 to child6;
  20.  
  21. --- creating a function----
  22. create or replace function fn_tbl_trigger()
  23.  returns trigger as '
  24. begin
  25. if tg_op = 'insert' then
  26.     if (new.entry_date > = DATE '2008-01-01' and new.entry_date < DATE '2008-03-01' )     
  27.  
  28.     THEN
  29.         INSERT INTO tbl_child1(new.*);
  30.     elseif (new.entry_date > = DATE '2008-03-01' and new.entry_date < DATE 
  31.  
  32. '2008-05-01' )         THEN
  33.         INSERT INTO tbl_child2(new.*);
  34.     elsif and so on for child3 to child6;
  35.     end if;
  36. end if;
  37. return null;
  38. end;
  39. '
  40. language plpgsql;
  41.  
  42. ---creating trigger---
  43. create trigger tbl_trig
  44. before insert on tbl_mst
  45. for each row
  46. execute procedure fn_tbl_trigger();
i think this will clear u.
Mar 25 '08 #3

Expert 100+
P: 700
here's my code
---the master table --

create table tbl_mst(
id varchar(20) not null,
name varchar(50),
entry_date date);

----the child tables ------
create tbl_child1(
check(entry_date >= DATE '2008-01-01' AND entry_date < '2008-03-01')
) INHERITS (tbl_mst);

create tbl_child2(
check(entry_date >= DATE '2008-03-01' AND entry_date < '2008-05-01')
) INHERITS (tbl_mst);
and so on tbl_child3 to tbl_child6;

----assigning primary key to child tables------
alter table tbl_child1 add constraints pk_child1(id);
alter table tbl_child1 add constraints pk_child2(id);
and so on for child3 to child6;

--- creating a function----
create or replace function fn_tbl_trigger()
returns trigger as '
begin
if tg_op = 'insert' then
if (new.entry_date > = DATE '2008-01-01' and new.entry_date < DATE '2008-03-01' )

THEN
INSERT INTO tbl_child1(new.*);
elseif (new.entry_date > = DATE '2008-03-01' and new.entry_date < DATE

'2008-05-01' ) THEN
INSERT INTO tbl_child2(new.*);
elsif and so on for child3 to child6;
end if;
end if;
return null;
end;
'
language plpgsql;


---creating trigger---
create trigger tbl_trig
before insert on tbl_mst
for each row
execute procedure fn_tbl_trigger();

i think this will clear u.
Maybe try into trigger something like that (you just need to extend the following example for all six child tables)

Expand|Select|Wrap|Line Numbers
  1. create or replace function ....
  2. declare
  3. r record;
  4. ...
  5. begin
  6. ...
  7.         select into r * from tbl_child1 where NEW.id =id
  8.         if found then              
  9.                 return NEW;
  10.         else
  11.                 return NULL;
  12.         end if;
  13. ...
  14. end;
  15.  
it's late so i'm not sure if it is clear. Is it?
Mar 25 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.