I'm trying to implement the following trigger on an Oracle 9i database
(running on Solaris 8) with three tables. The tables are
LR_READER
LR_ATTRIBUTE
LR_READERATTRIBUTE
I get an error whenever I try to insert/update/delete the ATTRIBUTEID
foreign key in LR_READERATTRIBUTE. The error says: "ORA-04098:
trigger 'SCOTTFORESMAN.VALIDATEFK_5B512FC0' is invalid and failed
re-validation"
Full source code is included at the bottom of the message for context,
but this snippet just below is the one that appears to be throwing the
error.
--Before Update/Insert Validate Foreign Key.
create trigger VALIDATEFK_5B512FC0
before insert or update on LR_READERATTRIBUTE
referencing OLD as old NEW as new
for each row when (
new.ATTRIBUTEID IS NOT NULL )
declare
pk_not_found EXCEPTION;
mutating_table EXCEPTION;
PRAGMA EXCEPTION_INIT (mutating_table,-4091);
parent_row LR_ATTRIBUTE%ROWTYPE;
cursor c1 (fkVal1 NUMBERPS) is
SELECT *
FROM LR_ATTRIBUTE WHERE
ID = fkVal1
for update of
ID;
begin
open c1(:new.ATTRIBUTEID);
fetch c1 into parent_row;
if c1%NOTFOUND then
raise pk_not_found;
end if;
close c1;
exception
when pk_not_found then
close c1;
raise_application_error(-20000,'Invalid FK value');
when mutating_table then
NULL;
when others then
close c1;
raise;
end;
FULL SOURCE CODE
- This SQL DDL script was generated by Microsoft Visual Studio
(Release Date: LOCAL BUILD).
- Driver Used : Microsoft Visual Studio - Oracle Server Driver.
- Document : C:\Documents and Settings\uwatsr2\My
Documents\External Projects\Online Leveled Readers Searchable
Database\Erd2.vsd.
- Time Created: June 09, 2004 7:46 AM.
- Operation : From Visio Generate Wizard.
- Connected data source : No connection.
- Connected server : No connection.
- Connected database : Not applicable.
- Create new table LR_READER.
- LR_READER : Table of LR_READER
- ISBN : ISBN identifies LR_READER
- TITLE : title of Reader
create table LR_READER (
ISBN CHAR(10) not null,
TITLE VARCHAR2(100) not null, constraint LR_READER_PK primary key
(ISBN)) TABLESPACE LREADER;
- Create new table LR_ATTRIBUTE.
- LR_ATTRIBUTE : Table of LR_ATTRIBUTE
- ID : ID identifies LR_ATTRIBUTE
- "VALUE" : VALUE is of LR_ATTRIBUTE
- PARENTID : PARENTID is of LR_ATTRIBUTE
create table LR_ATTRIBUTE (
ID NUMBER(10,0) not null,
"VALUE" VARCHAR2(255) not null,
PARENTID NUMBER(38,0) null, constraint LR_ATTRIBUTE_PK primary key
(ID)) TABLESPACE LREADER;
create sequence LR_ATTRIBUTE_SEQ;
CREATE OR REPLACE TRIGGER ID_TRIG_ATTRIBUTE
BEFORE INSERT
ON LR_ATTRIBUTE
FOR EACH ROW
BEGIN
SELECT LR_ATTRIBUTE_SEQ.NEXTVAL
INTO :NEW.ID
FROM DUAL;
END;
/
- Create new table LR_READERATTRIBUTE.
- LR_READERATTRIBUTE : Table of LR_READERATTRIBUTE
- ID : ID identifies LR_READERATTRIBUTE
- ISBN : ISBN partly identifies LR_READERATTRIBUTE
- ATTRIBUTEID : ATTRIBUTEID partly identifies LR_READERATTRIBUTE
create table LR_READERATTRIBUTE (
ID NUMBER(10,2) not null,
ISBN CHAR(10) not null,
ATTRIBUTEID NUMBER(38,0) not null, constraint LR_READERATTRIBUTE_PK
primary key (ID)) TABLESPACE LREADER;
create sequence LR_READERATTRIBUTE_SEQ;
CREATE OR REPLACE TRIGGER ID_TRIG_READERATTRIBUTE
BEFORE INSERT
ON LR_READERATTRIBUTE
FOR EACH ROW
BEGIN
SELECT LR_READERATTRIBUTE_SEQ.NEXTVAL
INTO :NEW.ID
FROM DUAL;
END;
/
- Add the remaining keys, constraints and indexes for the table
LR_READER.
create index LR_READER_TITLE on LR_READER (
TITLE ASC);
- Add the remaining keys, constraints and indexes for the table
LR_ATTRIBUTE.
create index LR_ATTRIBUTEVALUE on LR_ATTRIBUTE (
"VALUE" ASC);
- Add foreign key constraints to table LR_READERATTRIBUTE.
alter table LR_READERATTRIBUTE
add constraint ATTRIBUTEREADERATTRIBUTE foreign key (
ATTRIBUTEID)
references LR_ATTRIBUTE (
ID) DISABLE;
alter table LR_READERATTRIBUTE
add constraint READERREADERATTRIBUTE foreign key (
ISBN)
references LR_READER (
ISBN) DISABLE;
--Before Update/Insert Validate Foreign Key.
create trigger VALIDATEFK_5B512FC0
before insert or update on LR_READERATTRIBUTE
referencing OLD as old NEW as new
for each row when (
new.ATTRIBUTEID IS NOT NULL )
declare
pk_not_found EXCEPTION;
mutating_table EXCEPTION;
PRAGMA EXCEPTION_INIT (mutating_table,-4091);
parent_row LR_ATTRIBUTE%ROWTYPE;
cursor c1 (fkVal1 NUMBERPS) is
SELECT *
FROM LR_ATTRIBUTE WHERE
ID = fkVal1
for update of
ID;
begin
open c1(:new.ATTRIBUTEID);
fetch c1 into parent_row;
if c1%NOTFOUND then
raise pk_not_found;
end if;
close c1;
exception
when pk_not_found then
close c1;
raise_application_error(-20000,'Invalid FK value');
when mutating_table then
NULL;
when others then
close c1;
raise;
end;
- Trigger: Cascade updates to child table.
create trigger ONUPDATECASCADE_5B508A2C
after update of ID on LR_ATTRIBUTE
referencing OLD as old NEW as new
for each row
begin
UPDATE LR_READERATTRIBUTE
SET
LR_READERATTRIBUTE.ATTRIBUTEID = :new.ID
WHERE
LR_READERATTRIBUTE.ATTRIBUTEID = :old.ID;
end;
- Trigger: Restrict deletes of referenced key.
create trigger RESTRICT_5B508A2C
before delete on LR_ATTRIBUTE
referencing OLD as old NEW as new
for each row
declare
dependent_rows_exist EXCEPTION;
mutating_table EXCEPTION;
PRAGMA EXCEPTION_INIT (mutating_table,-4091);
childRow LR_READERATTRIBUTE%ROWTYPE;
cursor c1 is
SELECT *
FROM LR_READERATTRIBUTE WHERE
ATTRIBUTEID = :old.ID;
begin
open c1;
fetch c1 into childRow;
if c1%FOUND then
raise dependent_rows_exist;
end if;
close c1;
exception
when dependent_rows_exist then
close c1;
raise_application_error(-20001, 'Dependent rows exist in table
LR_READERATTRIBUTE');
when mutating_table then
close c1;
NULL;
end;
--Before Update/Insert Validate Foreign Key.
create trigger VALIDATEFK_2AA438F2
before insert or update on LR_READERATTRIBUTE
referencing OLD as old NEW as new
for each row when (
new.ISBN IS NOT NULL )
declare
pk_not_found EXCEPTION;
mutating_table EXCEPTION;
PRAGMA EXCEPTION_INIT (mutating_table,-4091);
parent_row LR_READER%ROWTYPE;
cursor c1 (fkVal1 CHAR) is
SELECT *
FROM LR_READER WHERE
ISBN = fkVal1
for update of
ISBN;
begin
open c1(:new.ISBN);
fetch c1 into parent_row;
if c1%NOTFOUND then
raise pk_not_found;
end if;
close c1;
exception
when pk_not_found then
close c1;
raise_application_error(-20000,'Invalid FK value');
when mutating_table then
NULL;
when others then
close c1;
raise;
end;
- Trigger: Cascade updates to child table.
create trigger ONUPDATECASCADE_30E40C83
after update of ISBN on LR_READER
referencing OLD as old NEW as new
for each row
begin
UPDATE LR_READERATTRIBUTE
SET
LR_READERATTRIBUTE.ISBN = :new.ISBN
WHERE
LR_READERATTRIBUTE.ISBN = :old.ISBN;
end;
- Trigger: Restrict deletes of referenced key.
create trigger RESTRICT_30E40C83
before delete on LR_READER
referencing OLD as old NEW as new
for each row
declare
dependent_rows_exist EXCEPTION;
mutating_table EXCEPTION;
PRAGMA EXCEPTION_INIT (mutating_table,-4091);
childRow LR_READERATTRIBUTE%ROWTYPE;
cursor c1 is
SELECT *
FROM LR_READERATTRIBUTE WHERE
ISBN = :old.ISBN;
begin
open c1;
fetch c1 into childRow;
if c1%FOUND then
raise dependent_rows_exist;
end if;
close c1;
exception
when dependent_rows_exist then
close c1;
raise_application_error(-20001, 'Dependent rows exist in table
LR_READERATTRIBUTE');
when mutating_table then
close c1;
NULL;
end;
- This is the end of the Microsoft Visual Studio generated SQL DDL
script.
Received on Wed Jun 09 2004 - 08:46:24 CDT
