[coolminded]

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

[rski]

Originally Posted by coolminded

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?

[coolminded]

Originally Posted by rski

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.

[rski]

Originally Posted by coolminded

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?