Home » SQL & PL/SQL » SQL & PL/SQL » Trigger problem
Trigger problem [message #229969] Tue, 10 April 2007 07:59 Go to next message
nafe74
Messages: 7
Registered: April 2007
Junior Member
Hi I am trying to create a trigger which prevents more than 2 of the same foreign key from one table being inserted in another, but get the following error:
ERROR at line 14: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

begin case declare end exception exit for goto if loop mod
null pragma raise return select update while with

The code for my trigger is as follows:
CREATE OR REPLACE TRIGGER Int_Check_Trigger BEFORE INSERT OR UPDATE ON Applications_Interfaces FOR EACH ROW
DECLARE
check number(2)
BEGIN
SELECT COUNT(*)
INTO check
FROM Applications_Interfaces
WHERE Interface_ID = :NEW.Interface_ID;
IF check >= 2
THEN RAISE_APPLICATION_ERROR (-20699, 'Int_Check_Trigger [' || SQLERRM || '] interface already has 2 applications');
END IF;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR (-20710, 'Int_Check_Trigger [' || SQLERRM || ']');
END;

Any help would be greatly appreciated!
Re: Trigger problem [message #229972 is a reply to message #229969] Tue, 10 April 2007 08:04 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
- Check is an Oracle reserved word. Choose another name.
- There's no semicolon where you declare this variable.
- There's -even better- no need for a trigger at all. Why don't you add a unique constraint on the column?

MHE

[Updated on: Tue, 10 April 2007 08:05]

Report message to a moderator

Re: Trigger problem [message #229973 is a reply to message #229969] Tue, 10 April 2007 08:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You cant' do it this way.

PL/SQL error: line "check number(2)" should be ended with a ";"
A PL/SQL statement is only executed if you type "/" at last line.

Regards
Michel

icon7.gif  Re: Trigger problem [message #229974 is a reply to message #229972] Tue, 10 April 2007 08:06 Go to previous messageGo to next message
nafe74
Messages: 7
Registered: April 2007
Junior Member
Thanx alot I had taken out the semi colon due to another error, but was all to do with check being a reserved word, thanks again!
Re: Trigger problem [message #229978 is a reply to message #229974] Tue, 10 April 2007 08:17 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Have you read my third remark? Why would you try to implement something that already exists. A UNIQUE key is what you are looking for.

MHE
Re: Trigger problem [message #229988 is a reply to message #229978] Tue, 10 April 2007 08:37 Go to previous messageGo to next message
nafe74
Messages: 7
Registered: April 2007
Junior Member
sorry missed that point, but how would that then allow for the key to be allowed to be used twice?
Re: Trigger problem [message #229990 is a reply to message #229988] Tue, 10 April 2007 08:40 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Right. I missed that part. My mistake.

MHE
Re: Trigger problem [message #229995 is a reply to message #229990] Tue, 10 April 2007 08:48 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Is this solution not going to cause quite a lot of mutating table errors?
Re: Trigger problem [message #230005 is a reply to message #229995] Tue, 10 April 2007 09:07 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes it will.

Regards
Michel
Previous Topic: forgot my system password
Next Topic: ORA-00984: column not allowed here ??
Goto Forum:
  


Current Time: Thu Dec 08 18:25:59 CST 2016

Total time taken to generate the page: 0.23252 seconds