| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Referrential integrity
On Tue, 04 Nov 1997 14:53:30 -0500, "Alex Vilner" <avilner_at_usa.net> wrote:
>Hello,
>
>We are using Oracle 8 for the database, and there are a few issues that we
>ran into with enforcing the refferential integrity that we would like to
>bounce off of Oracle gurus.
>
>1) When updating a table, we would like to be able to analyze other
>information inside the same table (stored in other rows). When issuing a
>SELECT against the table in-transition, we get the infamous "Mutating
>table" message. The trigger being defined as either BEFORE or AFTER does
>not make a difference. Is there an alternative way of doing this?
>
The way to do this is to defer reading the table until after all of the row level changes have been made. Below is an example. We use a package to maintain a state across the triggers. the first trigger, a BEFORE trigger, simply resets the package state to some know state. the second trigger collects all of the rowids affected by the update into a table. The third trigger contains all of the logic you want to perform for the affected rows. You will loop over the entries in the pl/sql table. My example computes the average salary by dept for each updated employee record.... showing you how to read the table using the saved rowid.
create or replace package emp_pkg
as
type ridArray is table of rowid index by binary_integer;
rids ridArray;
cnt number;
end;
/
create or replace trigger emp_bu
before update on emp
begin
emp_pkg.cnt := 0;
end;
/
create or replace trigger emp_aufer
after update on emp
for each row
begin
emp_pkg.cnt := emp_pkg.cnt + 1;
emp_pkg.rids( emp_pkg.cnt ) := :new.rowid;
end;
/
create or replace trigger emp_au
after update on emp
declare
avg_sal number;
begin
for i in 1 .. emp_pkg.cnt loop
select avg(sal) into avg_sal
from emp
where deptno = ( select deptno
from emp
where rowid = emp_pkg.rids(i) );
-- do something about it.....
dbms_output.put_line( emp_pkg.rids(i) || ' avg = ' || avg_sal );
end loop;
update emp set ename = upper(ename);
>2) When two tables are joined in the primary-foreign key relationship,
>cascading deletes or restricting deletes work fine. There is no way, to our
>best knowledge, to specify that the dependent table should have its foreign
>key column set to NULL when primary is deleted.
>
There is no declaritve way to do this in O8 but in O8, it can be done with a very simple trigger and a deferred constraint. Consider:
create table a ( a int primary key );
create table b ( b int primary key,
a int references a deferrable initially deferred );
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
create or replace package a_pkg
as
type numArray is table of number index by binary_integer;
old_a numArray;
cnt number;
end;
/
create or replace trigger a_bd
before delete on a
begin
a_pkg.cnt := 0;
end;
/
create or replace trigger a_bdfer
before delete on a
for each row
begin
a_pkg.cnt := a_pkg.cnt+1;
a_pkg.old_a(a_pkg.cnt) := :old.a;
end;
/
create or replace trigger a_ad
after delete on a
begin
for i in 1 .. a_pkg.cnt loop
update b set a = NULL where a = a_pkg.old_a(i);
end loop;
end;
/
insert into a values (1 );
insert into b values (1,1);
commit;
select * from b;
delete from a;
select * from b;
commit;
>3) In the situation with the primary-foreign keys another issue we are
>facing is when upon inserting into the primary table we need to insert
>several rows into the dependent table. It seems that even when AFTER INSERT
>trigger is executed, the row is still NOT in the primary table, and so the
>refferential integrity constraint fails on an insert into dependent table.
>
>Oh, and finally, is there an easy way of debugging a trigger? If a stored
>procedure compiles with errors, one can view them using the SHOW ERRORs
>command (or use the Schema Manager). There is nothing we found that would
>do the trick for triggers.
>
SQL> show errors trigger <trigger_name>
will do it
>We tried declaring stored procedures, using the SQL from triggers to
>eliminate some of the compile errors, but the problem is that triggers also
>use some trigger-specific syntax, like :NEW and :OLD, which do not work
>inside SPs.
>
>Any suggestions to any of the possible workarounds would be greatly
>appreciated. Unless we are doing something radically wrong, it seems
>surprising that a famous and popular database, like ORACLE, would have
>such issues that cannot be easily resolved. Thank you all in advance!
>
>Alex Vilner
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri Nov 07 1997 - 00:00:00 CST
 |
 |