Home » SQL & PL/SQL » SQL & PL/SQL » Implementing 1 to many mandatory relatioship
Implementing 1 to many mandatory relatioship [message #209534] Fri, 15 December 2006 07:06 Go to next message
provola74
Messages: 16
Registered: November 2006
Junior Member
hi to all, I'd like to implement a 1-many mandatory rel. both sides.
Here's an example:
+---------+
| pippoes |
+---------+
| pippoid | prim. k.
-----------
|123      |
+---------+
     | (1) mand.
     | (m) mand.
+---------+
| plutoes |
+---------+
|plpk|data| resp. foreign k. and data for plutoes
+---------+
| 123| 1  |
| 123| 2  |
+---------+


before commiting the insertion of records, no error must occur.
after commiting an error must occur if there is no pluto for, at least, one pippo!!!

I can't find any trigger logiv doing that...
...can u help me please?

regards
Provola74
Re: Implementing 1 to many mandatory relatioship [message #209537 is a reply to message #209534] Fri, 15 December 2006 07:23 Go to previous messageGo to next message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Unless I didn't understand you correctly, you don't need triggers to do that - default master-detail relationship through foreign key constraint should be enough.
CREATE TABLE PIPPOES
(pippoid NUMBER PRIMARY KEY);

CREATE TABLE PLUTOES
(plpk NUMBER CONSTRAINT fk_pl_pi REFERENCES PIPPOES (pippoid),
 data NUMBER
);
Re: Implementing 1 to many mandatory relatioship [message #209538 is a reply to message #209537] Fri, 15 December 2006 07:32 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I think the OP is looking for a minimum of one PLUTOES record for each PIPPOES record.

I don't think you can do that without a trigger.
Re: Implementing 1 to many mandatory relatioship [message #209549 is a reply to message #209537] Fri, 15 December 2006 08:48 Go to previous messageGo to next message
provola74
Messages: 16
Registered: November 2006
Junior Member
the relationship is this
pippoes (1) <----> (m) plutoes

mandatory means that 1 pippoes can't exists if there is no plutoes (at least one).

when u use
CREATE TABLE PIPPOES
(pippoid NUMBER PRIMARY KEY);

CREATE TABLE PLUTOES
(plpk NUMBER CONSTRAINT fk_pl_pi REFERENCES PIPPOES (pippoid),
 data NUMBER
);



all plutoes are optional and so if u do:

insert into pippoes values(123);commit;


no error occurs, instead the insert operation into pippoes must be followed by, at least one, insert operation into plutoes.
If this doesn't happen an error must occur!!!

regards

ptovola74
Re: Implementing 1 to many mandatory relatioship [message #209550 is a reply to message #209538] Fri, 15 December 2006 08:50 Go to previous messageGo to next message
provola74
Messages: 16
Registered: November 2006
Junior Member
JRowbottom wrote on Fri, 15 December 2006 07:32
I think the OP is looking for a minimum of one PLUTOES record for each PIPPOES record.

I don't think you can do that without a trigger.


all checks must be do by the server side, the client can only receive an exception about violating this rules...
...ok let's use one or more triggers...
...how can i do that?

...
?

[Updated on: Fri, 15 December 2006 08:53]

Report message to a moderator

Re: Implementing 1 to many mandatory relatioship [message #209554 is a reply to message #209550] Fri, 15 December 2006 09:25 Go to previous messageGo to next message
provola74
Messages: 16
Registered: November 2006
Junior Member
I was thinging about the solution:

1) create a trigger after insert on all rows about pippoes that logs the inserted rows in a temp table.
2) create a trigger after all insert statement on plutoes that:
a - search for all logged pippos in the temp table, having at least one pluto in the plutoes table, deleting them if they exist.
b- if some pippo remains in the temp table then they doesn't have any plutos, so the transaction can be aborted by an exception and the remaing pippos in the temp table can be deleted.

create or repalce trigger pippo_log_trg
after insert on pippoes
for each row
begin
  --logging pippoes in pippo_log table
  insert into pippo_log values(:new.pippoid);
end;

create or replace trigger plutos_chk_trg
after insert on plutoes
declare
  first_pippoid pippoes.pippoid%type;
begin
  --deleting pippoes with at least a plutoes from pippo_log
  delete from pippo_log where pippoid in (select pippoid from plutoes);
  begin 
    --selection test: is there any pippo without plutoes?
    select pippoid into first_pippo from pippo_log where rowcount=1:
    exception when no_data_found then
      first_pippoid:=null;
  end;
  --after all, surely i can delete the pippo_log table
  delete from pippo_log;
  --if i found the one wrong pippo , i can raise an exec.
  if (first_pippoid is not null) then
      raise_application_error(-20000, 'There is a pippo without a pluto!!!');
  end if;
end;


Can this be right?


regards
Re: Implementing 1 to many mandatory relatioship [message #209573 is a reply to message #209554] Fri, 15 December 2006 11:00 Go to previous messageGo to next message
provola74
Messages: 16
Registered: November 2006
Junior Member
the solution i found, doesn't work if there are no insertion on plutos table...

...any other solution?

regards
Re: Implementing 1 to many mandatory relatioship [message #209588 is a reply to message #209534] Fri, 15 December 2006 13:23 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member


bspdb>@c:\sriwork\foo.sql;
bspdb>drop table plutoes;

Table dropped.

bspdb>drop table pippoes ;

Table dropped.

bspdb>
bspdb>CREATE TABLE PIPPOES
2 (pippoid NUMBER PRIMARY KEY,
3 ncnt number not null );

Table created.

bspdb>
bspdb>alter table pippoes add constraint ppcheck check ( ncnt > 0) ;

Table altered.

bspdb>
bspdb>
bspdb>
bspdb>CREATE TABLE PLUTOES
2 (plpk NUMBER CONSTRAINT fk_pl_pi REFERENCES PIPPOES (pippoid),
3 data NUMBER
4 );

Table created.

bspdb>
bspdb>
bspdb>
bspdb>
bspdb>create or replace trigger check4plutos
2 before insert or update
3 on pippoes
4 for each row
5 begin
6 select count(*) into :new.ncnt from plutoes
7 where plutoes.plpk=:new.pippoid ;
8 end;
9 /

Trigger created.

bspdb>
bspdb>show errors;
No errors.
bspdb>
bspdb>insert into pippoes (pippoid) values (9);
insert into pippoes (pippoid) values (9)
*
ERROR at line 1:
ORA-02290: check constraint (SYSTEM.PPCHECK) violated


bspdb>spool off;
Re: Implementing 1 to many mandatory relatioship [message #209589 is a reply to message #209534] Fri, 15 December 2006 13:26 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member


Ouch!

For inserts to work , we need to take out the FK or make it a deferred constraint.

Srini
Re: Implementing 1 to many mandatory relatioship [message #209605 is a reply to message #209589] Fri, 15 December 2006 17:37 Go to previous message
provola74
Messages: 16
Registered: November 2006
Junior Member
ok tanku...ur solution is good.
now i'm trying to use a materialized views, with checks, i think it could be the most clean way to do this kind of stuffs since the dbms will manage all the involved operation i checking.

tnku very much!!!
Previous Topic: NCLOB to Date - Urgent
Next Topic: Using & character in PL/SQL
Goto Forum:
  


Current Time: Mon Dec 05 23:49:25 CST 2016

Total time taken to generate the page: 0.09091 seconds