Home » SQL & PL/SQL » SQL & PL/SQL » Trigger with :new and RAISE_APPLIATION_ERROR (11.1.0.6.0)
Trigger with :new and RAISE_APPLIATION_ERROR [message #398923] Sun, 19 April 2009 16:51 Go to next message
hooharhar
Messages: 28
Registered: April 2009
Junior Member
Hello
I am struggling with simple trigger.
So far I have got

CREATE OR REPLACE TRIGGER Returns_Policy
BEFORE
INSERT ON Return
FOR EACH ROW
WHEN (new."ReasonForReturn" = 'test')
BEGIN
RAISE_APPLIATION_ERROR (-20001,'This is not a valid reason!');
END;


and I get this error:

Compilation failed, line 2 (22:45:25) The line numbers associated with compilation errors are relative to the first BEGIN statement. This only affects the compilation of database triggers.
PLS-00201: identifier 'RAISE_APPLIATION_ERROR' must be declaredCompilation failed, line 2 (22:45:25) The line numbers associated with compilation errors are relative to the first BEGIN statement. This only affects the compilation of database triggers.
PL/SQL: Statement ignored 


I could not find anything on the wiki about triggers under PL SQL. I believe I am meeting the required syntax for a FOR EACH ROW TRIGGER.

CREATE [OR REPLACE] TRIGGER trigger_name
timing
event1 [OR event2 OR event3]
ON table_name
FOR EACH ROW
[WHEN condition]
BEGIN
trigger_body
END;
/


the Return table looks like this:
CREATE TABLE  "RETURN" 
   (	"LineNo" NUMBER(8,0), 
	"OrderNo" NUMBER(8,0), 
	"DateReturned" DATE, 
	"ReasonForReturn" VARCHAR2(200), 
	"QuantityReturned" NUMBER(5,0) NOT NULL ENABLE, 
	 CONSTRAINT "PK_RETURN" PRIMARY KEY ("LineNo", "OrderNo", "DateReturned") ENABLE
   )
/


Can anyone point me in the right direction?
many thanks
Re: Trigger with :new and RAISE_APPLIATION_ERROR [message #398924 is a reply to message #398923] Sun, 19 April 2009 17:02 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
It is a really, really bad idea to use any keyword, such as RETURN, as an object name.
Please try again with a different table name.

It is also a bad idea to used Mixed Case names.

[Updated on: Sun, 19 April 2009 17:04]

Report message to a moderator

Re: Trigger with :new and RAISE_APPLIATION_ERROR [message #398925 is a reply to message #398923] Sun, 19 April 2009 17:16 Go to previous messageGo to next message
hooharhar
Messages: 28
Registered: April 2009
Junior Member
Oh yeah silly of me! I changed the table name to ReturnedItems and I still get the same error

create or replace TRIGGER Return_Policy
BEFORE
INSERT ON ReturnedItems
FOR EACH ROW
WHEN (new."ReasonForReturn" = 'test')
BEGIN
RAISE_APPLIATION_ERROR (-20001,'This is not a valid reason!');
END;


By mixed case do you mean I should have my table/column names something like table_name/column_name instead of TableName/ColumnName

Thanks for the advice
Re: Trigger with :new and RAISE_APPLIATION_ERROR [message #398926 is a reply to message #398923] Sun, 19 April 2009 17:27 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
The default behavior for Oracle is that object name are UPPER_CASE.


>PLS-00201: identifier 'RAISE_APPLIATION_ERROR' must be declared

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/raise_statement.htm#sthref2962
Re: Trigger with :new and RAISE_APPLIATION_ERROR [message #398927 is a reply to message #398923] Sun, 19 April 2009 17:42 Go to previous messageGo to next message
hooharhar
Messages: 28
Registered: April 2009
Junior Member
Oh No... I was spelling it wrong!

Quote:
RAISE_APPLIATION_ERROR


Just need to see if it works now...
many thanks! Cool
Re: Trigger with :new and RAISE_APPLIATION_ERROR [message #398928 is a reply to message #398923] Sun, 19 April 2009 17:46 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
FWIW - http://asktom.oracle.com has many, many fine coding examples.
Re: Trigger with :new and RAISE_APPLIATION_ERROR [message #398929 is a reply to message #398928] Sun, 19 April 2009 17:48 Go to previous message
hooharhar
Messages: 28
Registered: April 2009
Junior Member
Thankyou very much
Previous Topic: JOIN LOGIC
Next Topic: char vs length()
Goto Forum:
  


Current Time: Fri Dec 09 00:16:48 CST 2016

Total time taken to generate the page: 0.12731 seconds