Home » SQL & PL/SQL » SQL & PL/SQL » create foreign key trigger
create foreign key trigger [message #206342] Wed, 29 November 2006 12:33 Go to next message
kdt2006
Messages: 3
Registered: November 2006
Junior Member
hi
i want to create a trigger that will act like a foreign key and check whether the value being entered already exists in two separate tables. I want it to not allow entry if the id already exists in: either none of the tables; or both the tables.

I have tried to create the following trigger, it compiles, but doesnt do the job Sad

create or replace trigger trig_test1
before insert on TEST3
referencing old as old new as new
for each row
DECLARE
cursor cur_check_test1 is
select PKONE
from TEST1, TEST3
where PKONE = TEST3.PKTHREE;
CURSOR CUR_CHECK_TEST2 IS
SELECT PKTWO
FROM TEST2, TEST3
WHERE PKTWO = TEST3.PKTHREE;
v_key_conflict EXCEPTION;
v_test1_temp varchar2(1);
v_test2_temp varchar2(1);
BEGIN
OPEN cur_check_test1;
FETCH cur_check_test1 into v_test1_temp;
if (((cur_check_test1%FOUND) AND (cur_check_test2%found)) or ((cur_check_test1%notfound) and (cur_check_test2%notfound)))
then close cur_check_test1;
close cur_check_test2;
RAISE v_key_conflict;
else
close cur_check_test1;
close cur_check_test2;
end if;
EXCEPTION
WHEN v_key_conflict then
raise_application_error(-20111, 'parent key constraint violated');

end;

Can someone please help me out. cheers
Re: create foreign key trigger [message #206351 is a reply to message #206342] Wed, 29 November 2006 13:22 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
A couple of things:
- do not select from the triggering table; it is not needed, since you can refer to the inserted values by prefixing it with ":new."
- secondly, you refer to cur_check_test2%found before you even opened or fetched from cur_check_test2.

[Edit: And while we're at it, please take a look at the sticky. It contains tips on how to format your post so it is more readable.]

[Updated on: Wed, 29 November 2006 13:24]

Report message to a moderator

Re: create foreign key trigger [message #206376 is a reply to message #206342] Wed, 29 November 2006 15:53 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
and thirdly Razz unless it is a homework assignment, why would you not want to use a foreign key to enforce referential integrity. Coding such things in code is not a good idea.
Re: create foreign key trigger [message #206421 is a reply to message #206376] Thu, 30 November 2006 00:28 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
How would you create a normal FK to enforce the integrity described? FKs don't suffice for that..
Re: create foreign key trigger [message #206453 is a reply to message #206421] Thu, 30 November 2006 02:18 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'd create an On Commit snapshot on the two tables, so that we had all the data in one place, create a Unique or PK constraint on the columns we wanted, and then point the Foreign key at that.
Re: create foreign key trigger [message #206454 is a reply to message #206342] Thu, 30 November 2006 02:26 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Another problem with doing it as a trigger is that it won't work in a multi user environment:

Session 1 deleted value A from one of the tables acting as a PK, but doesn't commit.

Session 2 adds a new record referencing value A. The trigger fires and sees that Value A exists. Session 2 commits.

Session 1 commits, and you now have an orphaned record.
Re: create foreign key trigger [message #206455 is a reply to message #206453] Thu, 30 November 2006 02:26 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
But the reqs don't mention that the values over the two parent tables should be unique. Only that you may not refer to them if they are not unique..

[Edit: Mind you, I don't advocate the use of triggers!]

[Updated on: Thu, 30 November 2006 02:27]

Report message to a moderator

Previous Topic: ORA-1300
Next Topic: How to strip of characters
Goto Forum:
  


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

Total time taken to generate the page: 0.14579 seconds