Home » SQL & PL/SQL » SQL & PL/SQL » how can I user trigger to implement Referential Integrity in distributed database.
how can I user trigger to implement Referential Integrity in distributed database. [message #270903] Fri, 28 September 2007 05:37 Go to next message
milo0105
Messages: 4
Registered: September 2007
Junior Member
2 tables Dept_tab,Emp_tab are stored in database "cse5200a" and cse "cse5200b".
CREATE TABLE Emp_tab (
Empno NUMBER NOT NULL,
Ename VARCHAR2(10),
Job VARCHAR2(9),
Mgr NUMBER(4),
Hiredate DATE,
Sal NUMBER(7,2),
Comm NUMBER(7,2),
Deptno NUMBER(2) NOT NULL);

CREATE TABLE Dept_tab (
Deptno NUMBER(2) NOT NULL,
Dname VARCHAR2(14),
Loc VARCHAR2(13),
Mgr_no NUMBER,
Dept_type NUMBER);

I defined the trigger in cse5200b :


CREATE OR REPLACE TRIGGER Emp_dept_check
BEFORE INSERT OR UPDATE OF Deptno ON Emp_tab
FOR EACH ROW WHEN (new.Deptno IS NOT NULL)

DECLARE
Dummy INTEGER; -- to be used for cursor fetch
Invalid_department EXCEPTION;
Valid_department EXCEPTION;
Mutating_table EXCEPTION;
PRAGMA EXCEPTION_INIT (Mutating_table, -4091);

CURSOR Dummy_cursor (Dn NUMBER) IS
SELECT Deptno FROM Dept_tab@cse5200A
WHERE Deptno = Dn
FOR UPDATE OF Deptno;
BEGIN
OPEN Dummy_cursor (:new.Deptno);
FETCH Dummy_cursor INTO Dummy;

IF Dummy_cursor%NOTFOUND THEN
RAISE Invalid_department;
ELSE
RAISE valid_department;
END IF;
CLOSE Dummy_cursor;
EXCEPTION
WHEN Invalid_department THEN
CLOSE Dummy_cursor;
Raise_application_error(-20000, 'Invalid Department'
|| ' Number' || TO_CHAR(:new.deptno));
WHEN Valid_department THEN
CLOSE Dummy_cursor;
WHEN Mutating_table THEN
NULL;
END;


it seems that the cursor can not access the table "Dept_tab" of database A. when i built this two table in a same database, trigger could be created without any problem. why can't i do the same thing in a distributed system?
Re: how can I user trigger to implement Referential Integrity in distributed database. [message #270907 is a reply to message #270903] Fri, 28 September 2007 05:57 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What error do you get.
Re: how can I user trigger to implement Referential Integrity in distributed database. [message #270909 is a reply to message #270903] Fri, 28 September 2007 06:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Regards
Michel
Re: how can I user trigger to implement Referential Integrity in distributed database. [message #270928 is a reply to message #270909] Fri, 28 September 2007 07:40 Go to previous messageGo to next message
milo0105
Messages: 4
Registered: September 2007
Junior Member
I got error message
trigger created with complication errors
Re: how can I user trigger to implement Referential Integrity in distributed database. [message #270930 is a reply to message #270928] Fri, 28 September 2007 07:42 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Thats not really an error, that's just telling you that you HAVE errors.
After compilation type
Sho Err
and paste what appears
Re: how can I user trigger to implement Referential Integrity in distributed database. [message #270934 is a reply to message #270930] Fri, 28 September 2007 07:55 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
On a related note, do you have any idea how stupid this piece of code is:
WHEN Mutating_table THEN
NULL;


If your trigger gives you mutating_table errors, it's a strong sign that there is a fundamental flaw in your design. As not all inserts/updates will trigger the mutating table error (ie for some DML the trigger will finish firing, and for others it will abotr part way through) by ignoring the exception you are saying that it doesn't matter whether the trigger finishes or not.

Re: how can I user trigger to implement Referential Integrity in distributed database. [message #270935 is a reply to message #270934] Fri, 28 September 2007 07:57 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
In reply to your title:
how can I user trigger to implement Referential Integrity in distributed database.

I would say that as you cannot reliably use triggers to implement Referential Integrity in a non-distributed database, your chances of doing it in a distributed environment are petty much nill.
Re: how can I user trigger to implement Referential Integrity in distributed database. [message #270942 is a reply to message #270903] Fri, 28 September 2007 08:42 Go to previous messageGo to next message
milo0105
Messages: 4
Registered: September 2007
Junior Member
actually, I am implementing Referential Integrity by triggers in a distributed database. This trigger is stored in cse5200b. but when i wrote the cursor like "CURSOR Dummy_cursor (Dn NUMBER) IS
SELECT Deptno FROM Dept_tab@cse5200A", this error happed. when i stored the two table in one database either cse5200a or cse5200b, I can implement this trigger successfully by writing the cursor like "CURSOR Dummy_cursor (Dn NUMBER) IS
SELECT Deptno FROM Dept_tab".
Re: how can I user trigger to implement Referential Integrity in distributed database. [message #270943 is a reply to message #270934] Fri, 28 September 2007 08:44 Go to previous messageGo to next message
milo0105
Messages: 4
Registered: September 2007
Junior Member
JRowbottom wrote on Fri, 28 September 2007 07:55

If your trigger gives you mutating_table errors, it's a strong sign that there is a fundamental flaw in your design. As not all inserts/updates will trigger the mutating table error (ie for some DML the trigger will finish firing, and for others it will abotr part way through) by ignoring the exception you are saying that it doesn't matter whether the trigger finishes or not.



thanks, but I think mutating_table errors is not the problem here. acturally I can delete this excepiton here.
Re: how can I user trigger to implement Referential Integrity in distributed database. [message #270945 is a reply to message #270943] Fri, 28 September 2007 09:16 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
They were just other points worth noting while we wait for you to actally tell us what the error in your trigger is.
SELECT * 
FROM   ALL_ERRORS 
WHERE  NAME = 'EMP_DEPT_CHECK'


Why does the trigger open the Cursor FOR UPDATE?

I am certain that your referential integrity can be violated very easily, by something like:

Session 1 Inserts DEPT record on DB A and commits.

Session 2 Inserts EMP record on DB B. Check is made that the DEPT exists on Db B, but record is not yet committed.

Session 1 Deletes DEPT record on Db A. Checks and finds no Emp records using this dept_no on DB B, as session 2 has not yet committed. Commits the delete.

Session 2 Commits, and you've got an Emp record with no associated Dept.

I reckon you'd be better off using Mview based replication at an On Commit level, and using proper RI constraints onto the Materialized views.
Previous Topic: sql select where clause not working
Next Topic: Script to create synonyms for users granted a particular role
Goto Forum:
  


Current Time: Sun Dec 11 04:24:23 CST 2016

Total time taken to generate the page: 0.15028 seconds