| Trigger insuring parent records inserted before progeny [message #372205] |
Thu, 18 January 2001 16:37  |
Gene K. Smith
Messages: 1
Registered: January 2001
|
Junior Member |
|
|
|
Parent table holds genetic data of both parents and progeny. One column has the Plant_id (PK) of the plant and two others hold the plant_id of the parents in columns M_ID and F_ID. Tree progeny should not be entered into the table before its parents. Thus values of plantid should not be inserted into the columns M_ID and F_ID unless those values are already in the column Plant_id. How do I write a trigger to do this? Must it be a forms trigger or can it reside in the database as a table or database trigger?
|
|
|
|
| Re: Trigger insuring parent records inserted before progeny [message #372235 is a reply to message #372205] |
Sat, 20 January 2001 18:38  |
Robert Moy
Messages: 15
Registered: December 2000
|
Junior Member |
|
|
Hello Gene:
You can use this trigger to do that if I understand your question correctly. However, if Plant_id is a primary key, then it should stop the insert.
SQL> create or replace trigger stop2
2 before insert on factory
3 for each row
4 declare
5 dup_flag integer;
6 begin
7 If :new.Plant_id is null and :new.F_id = 'F' then
8 raise_application_error(-20000,'Must insert a Plant_id first');
9 end if;
10 If :new.Plant_id is null and :new.M_id = 'M' then
11 raise_application_error(-20000,'Must insert a Plant_id first');
12 end if;
13 end;
14 /
Trigger created.
SQL> insert into factory(M_id,F_id,name)
2 values ('002',' ','Phil');
insert into factory(M_id,F_id,name)
*
ERROR at line 1:
ORA-20000: Must insert a Plant_id first
ORA-06512: at "DAD.STOP2", line 8
ORA-04088: error during execution of trigger 'DAD.STOP2'
Good Luck
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
