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 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
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 #388751 is a reply to message #388655] Thu, 26 February 2009 01:30 Go to previous messageGo to next message
_jum
Messages: 509
Registered: February 2008
Senior Member
@Kevin - what is the difeference between the two examples ? The second example gives ORA-02291 error too IMHO?

[Updated on: Thu, 26 February 2009 01:42]

Report message to a moderator

Re: Trigger for enforcing referential integrity [message #388763 is a reply to message #388751] Thu, 26 February 2009 02:26 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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;
Re: Trigger for enforcing referential integrity [message #388768 is a reply to message #388766] Thu, 26 February 2009 02:59 Go to previous message
_jum
Messages: 509
Registered: February 2008
Senior Member
Thanks @JRowbottom for clarify this, I did misread the OP Embarassed
Previous Topic: WIn XP, Oracle 10g
Next Topic: Performance of cursor
Goto Forum:
  


Current Time: Fri Dec 09 00:05:56 CST 2016

Total time taken to generate the page: 0.05461 seconds