Home » SQL & PL/SQL » SQL & PL/SQL » Trigger for enforcing referential integrity (Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 )
Trigger for enforcing referential integrity [message #388351] |
Tue, 24 February 2009 07:27  |
STEFFEE
Messages: 1 Registered: February 2009
|
Junior Member |
|
|
I am having trouble setting up a trigger to enable me to enforce referential integrity. I am choosing a trigger as I do not believe it is possible to set it up using constraints.
My parent table (HAB)is of the following structure:
H_ID NUMBER PK
H_CODE VARCHAR2
H_DESCRIPTION
The child table (ATT) is as follows
so_id NUMBER PK
COL_NAME VARCHAR2 PK
VALUE VARCHAR
The table ATT contains in some records information that refers to the HAB table, in these instances col_name = 'HAB' (it can also contain many other value that do not refer to the HAB table)
When the COL_NAME = 'HAB' I want to check that the VALUE column contains a value that is present in HAB.H_ID.
Is it possible to do this with a trigger, I can not figure out how to add a where clause so I only apply the trigger when the col_name column = 'HAB'.
Below is my code so far:
CREATE OR REPLACE TRIGGER TRG_BRYO_ATT_HAB
BEFORE INSERT OR UPDATE
OF VALUE
ON SO_BRY_ATT
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
WHEN
(NEW.VALUE IS NOT NULL)
DECLARE
Dummy INTEGER; -- used for cursor fetch below
Invalid_habitat EXCEPTION;
Valid_habitat EXCEPTION;
Mutating_table EXCEPTION;
PRAGMA EXCEPTION_INIT (Mutating_table, -4091);
CURSOR Dummy_cursor (hc NUMBER) IS
SELECT h_id FROM hab
WHERE h_id = hc
FOR UPDATE OF h_id;
BEGIN
OPEN Dummy_cursor (:New.value);
FETCH Dummy_cursor INTO Dummy;
IF Dummy_cursor%NOTFOUND THEN
RAISE Invalid_hab;
ELSE
RAISE valid_hab;
END IF;
CLOSE Dummy_cursor;
EXCEPTION
WHEN Invalid_hab THEN
CLOSE Dummy_cursor;
Raise_application_error(-20000, 'Invalid hab'
|| ' code' || TO_CHAR(:New.value));
WHEN Valid_hab THEN
CLOSE Dummy_cursor;
WHEN Mutating_table THEN
NULL;
END TRG_BRYO_ATT_HAB;
/
|
|
|
Re: Trigger for enforcing referential integrity [message #388353 is a reply to message #388351] |
Tue, 24 February 2009 07:45   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Add it into the WHEN clause, or check that :NEW.col_name = 'HAB'
Also, get rid of that Exception trap for mutating table errors - a mutating table error is there to tell you that you're doing something terribly, terribly wrong, and that there is no guarantee that your data is going to be correct if you continue - trapping it rather than fixing your design is possibly the most stupid thing I've seen this year.
|
|
|
Re: Trigger for enforcing referential integrity [message #388655 is a reply to message #388351] |
Wed, 25 February 2009 20:51   |
 |
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
if I understand your situation, maybe this would be helpful. This example shows one way to use a constraint for your purpose.
SQL> drop table b;
Table dropped.
SQL> drop table a;
Table dropped.
SQL>
SQL> create table a (a varchar2(1));
Table created.
SQL>
SQL> create table b (b varchar2(1), c varchar2(1), a varchar2(1), a2 varchar2(1));
Table created.
SQL>
SQL> alter table a add primary key (a);
Table altered.
SQL> alter table b add primary key (b);
Table altered.
SQL> alter table b add foreign key (a2) references a;
Table altered.
SQL>
SQL> create or replace trigger bir_b
2 before insert on b
3 for each row
4 begin null;
5 if :new.c = '1' then :new.a2 := :new.a; else :new.a2 := null; end if;
6 end;
7 /
Trigger created.
SQL> show errors
No errors.
SQL>
SQL> insert into a (a) values ('X');
1 row created.
SQL>
SQL> insert into b (b,c,a) values ('1','0','1');
1 row created.
SQL> insert into b (b,c,a) values ('2','0','2');
1 row created.
SQL> insert into b (b,c,a) values ('3','1','Y');
insert into b (b,c,a) values ('3','1','Y')
*
ERROR at line 1:
ORA-02291: integrity constraint (KEVIN.SYS_C0010678) violated - parent key not
found
SQL> insert into b (b,c,a) values ('4','1','X');
1 row created.
SQL>
drop table b;
drop table a;
create table a (a varchar2(1));
create table b (b varchar2(1), c varchar2(1), a varchar2(1), a2 varchar2(1));
alter table a add primary key (a);
alter table b add primary key (b);
alter table b add foreign key (a2) references a;
create or replace trigger bir_b
before insert on b
for each row
begin null;
if :new.c = '1' then :new.a2 := :new.a; else :new.a2 := null; end if;
end;
/
show errors
insert into a (a) values ('X');
insert into b (b,c,a) values ('1','0','1');
insert into b (b,c,a) values ('2','0','2');
insert into b (b,c,a) values ('3','1','Y');
insert into b (b,c,a) values ('4','1','X');
The above works because Oracle does not check the constraint if any part of the foreign key is null. Someone smarter than me might even be able to show you how to do it without the trigger.
good luck, Kevin
|
|
|
|
Re: Trigger for enforcing referential integrity [message #388763 is a reply to message #388751] |
Thu, 26 February 2009 02:26   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
There is only one example - the first part of the post shows the code in action.
Kevin is showing a way of doing a conditional FK relationship, and the exception shows the FK constraint only being applied when the value of column C is 1.
|
|
|
Re: Trigger for enforcing referential integrity [message #388766 is a reply to message #388655] |
Thu, 26 February 2009 02:36   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
You could create an On Commit MV on table B, with the same structure as B, and conditionally populate the A2 column in the same way that the trigger does.
This would provide a check at Commit - equivalent to a deferred FK constraint:drop table b;
drop table a;
create table a (a varchar2(1));
create table b (b varchar2(1), c varchar2(1), a varchar2(1), a2 varchar2(1));
alter table a add primary key (a);
alter table b add primary key (b);
create materialized view log on b;
create materialized view b_mv refresh fast on commit as
select b
,c
,a
,case when c='1' then a else null end as a2
from b;
alter table b_mv add foreign key (a2) references a;
insert into a (a) values ('X');
insert into b (b,c,a) values ('1','0','1');
insert into b (b,c,a) values ('2','0','2');
insert into b (b,c,a) values ('3','1','Y');
insert into b (b,c,a) values ('4','1','X');
commit;
|
|
|
|
Goto Forum:
Current Time: Thu Feb 06 12:51:38 CST 2025
|