Re: Referrential integrity

From: Matthias Gresz <GreMa_at_t-online.de>
Date: 1997/11/05
Message-ID: <63p5un$a2$1@news00.btx.dtag.de>

Alex Vilner 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?
>
> 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.
>
> 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.
>
> 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

Hi,

Q2:

We had the task to normalize a table used in ACCESS for use in ORACLE. This table had "chapters" under each where several entires. We split that table into a tabel "Chapter" and a tabel "entries" with Fk inot chapter. I just copy the SQL-Statements to create and populate the "chapter" and "entries"-tables. Don't mind the german fieldnames.

Create Table KundengruppenUeberschriften (

	ID			NUMBER(9) Not Null,
	Ueberschrift		Varchar2(40) Not Null
)
INITRANS	4
MAXTRANS	10
PCTFREE		30
PCTUSED		60
STORAGE (
	INITIAL 10M
	NEXT	500K
	PCTINCREASE 10
	MINEXTENTS 1
	MAXEXTENTS 121
	)

TABLESPACE MAKLER; Alter Table Kundengruppenueberschriften
ADD ( PRIMARY KEY (ID) ) ; Create Table Kundengruppen
(

	ID			NUMBER(9) Not Null,
	Ueberschrift		NUMBER(9) Not Null,
	Kundengruppe		Varchar2(40) Not Null
)
INITRANS	4
MAXTRANS	10
PCTFREE		30
PCTUSED		60
STORAGE (
	INITIAL 10M
	NEXT	500K
	PCTINCREASE 10
	MINEXTENTS 1
	MAXEXTENTS 121
	)

TABLESPACE MAKLER; Alter Table Kundengruppen
ADD ( PRIMARY KEY (ID) ) ; Drop Sequence SEQ_KUNDENGRPUEBERSCHRIFTEN_ID; create Sequence SEQ_KUNDENGRPUEBERSCHRIFTEN_ID;

Drop Sequence SEQ_KUNDENGRUPPEN_ID;
create Sequence SEQ_KUNDENGRUPPEN_ID;

create or replace trigger tI_KUNDENGRPUEBERSCHRIFTEN before INSERT on KUNDENGRUPPENUEBERSCHRIFTEN for each row declare

	Lower number(9);
	Upper number(9);
begin
	Lower := :new.ID;
	SELECT NVL(MIN(Zaehler), 0) into Upper from STAMMDATEN
		WHERE KUNDENGRUPPEN IS NOT NULL 
			AND SUBSTR(KUNDENGRUPPEN, 1, 1) <> ' '
			AND ZAEHLER > :OLD.ID;
	if Upper = 0 then
		INSERT INTO KUNDENGRUPPEN
		SELECT 0 AS ID, Lower AS UEBERSCHRIFT, LTRIM(RTRIM(KUNDENGRUPPEN, '
'), ' ') AS KUNDENGRUPPE FROM STAMMDATEN
			WHERE KUNDENGRUPPEN IS NOT NULL 
				AND SUBSTR(KUNDENGRUPPEN, 1, 1) = ' '
				AND ZAEHLER > LOWER;
	else
		INSERT INTO KUNDENGRUPPEN
		SELECT 0 AS ID, Lower AS UEBERSCHRIFT, LTRIM(RTRIM(KUNDENGRUPPEN, '
'), ' ') AS KUNDENGRUPPE FROM STAMMDATEN
			WHERE KUNDENGRUPPEN IS NOT NULL 
				AND SUBSTR(KUNDENGRUPPEN, 1, 1) = ' '
				AND ZAEHLER > LOWER
				AND ZAEHLER < UPPER;
	end if;

end;
/

create or replace trigger tI_KUNDENGRUPPEN before INSERT on KUNDENGRUPPEN for each row
declare NeueNummer number(9);
begin

	Select SEQ_KUNDENGRUPPEN_ID.NEXTVAL into NeueNummer from dual;	
	:new.ID  := NeueNummer ; 

end;
/

INSERT INTO KUNDENGRUPPENUEBERSCHRIFTEN
        SELECT ZAEHLER AS ID, LTRIM(RTRIM(KUNDENGRUPPEN, ' '), ' ') AS UEBERSCHRIFT FROM STAMMDATEN

		WHERE KUNDENGRUPPEN IS NOT NULL 
			AND SUBSTR(KUNDENGRUPPEN, 1, 1) <> ' ';

Alter table Kundengruppen
ADD ( CONSTRAINT CON_KUNDENGRUPPEN FOREIGN KEY (Ueberschrift)

               REFERENCES Kundengruppenueberschriften) ;

create or replace trigger tI_KUNDENGRPUEBERSCHRIFTEN before INSERT on KUNDENGRUPPENUEBERSCHRIFTEN for each row declare NeueNummer number(9);
begin

        Select SEQ_KUNDENGRPUEBERSCHRIFTEN_ID.NEXTVAL into NeueNummer from dual;

        :new.ID := NeueNummer ;
end;
/

-- 
Regards

M.Gresz    :-)

Received on Wed Nov 05 1997 - 00:00:00 CST