Home » SQL & PL/SQL » SQL & PL/SQL » Re: I got a problem while writing a simple procedure (split from hijacked thread by bb)
Re: I got a problem while writing a simple procedure (split from hijacked thread by bb) [message #550628] Wed, 11 April 2012 02:33 Go to next message
rashmi85
Messages: 7
Registered: March 2012
Location: mumbai
Junior Member
--create table student (id number,name varchar2(10),std number(10));

--insert into student values (1,'aashu',10);
--insert into student values (2,'ramu',8);

----------------------------------------------------------------
create or replace trigger trig_conditional_pre
before insert or delete on student

declare
 std number;

begin
 if inserting then
   if not std in (1,2,3,4,5,6,7,8,9,10) then
    dbms_output.put_line('not a valid standard);
   end if;

 elsif deleting then
   raise_application_error(-20000,'u r not authorized person');
 end if;
end;

----------------------------------------------------------------

i have got an error as

11/36 PLS-00103: Encountered the symbol "U" when expecting one of the f ollowing: ) , * & | = - + < / > at in is mod remainder not re m => .. <an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_ LIKE4_ LIKEC_ as between from using || multiset member SUBMULTISET_


[mod-edit: code tags added by bb; next time pleas add them yourself]

[Updated on: Wed, 11 April 2012 05:37] by Moderator

Report message to a moderator

Re:what is wrong in this simple trigger code... [message #550630 is a reply to message #550628] Wed, 11 April 2012 02:40 Go to previous messageGo to next message
Littlefoot
Messages: 19540
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Oracle, in this version, doesn't support use of IM speech (instead of "U R" you should have used "YOU ARE").




Just kidding!

Single quote is missing at the end of "Not a valid standard" message.
Re:what is wrong in this simple trigger code... [message #550633 is a reply to message #550630] Wed, 11 April 2012 02:59 Go to previous messageGo to next message
cookiemonster
Messages: 10930
Registered: September 2008
Location: Rainy Manchester
Senior Member
Once you've fixed that, ask yourself why you are comparing a null variable to a list of numbers.
Re:what is wrong in this simple trigger code... [message #550652 is a reply to message #550633] Wed, 11 April 2012 05:40 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7975
Registered: November 2002
Location: California, USA
Senior Member
Newly inserted values in triggers should be referenced as :new.column_name.
Re:what is wrong in this simple trigger code... [message #550653 is a reply to message #550652] Wed, 11 April 2012 05:43 Go to previous messageGo to next message
cookiemonster
Messages: 10930
Registered: September 2008
Location: Rainy Manchester
Senior Member
And if you want to check column values you need to use a row level trigger not a statement level one.
Re: I got a problem while writing a simple procedure (split from hijacked thread by bb) [message #550681 is a reply to message #550628] Wed, 11 April 2012 09:11 Go to previous messageGo to next message
rashmi85
Messages: 7
Registered: March 2012
Location: mumbai
Junior Member
ok..thank you for your suggestions friends..
but even after incorporating those changes in the script i am not getting the desired o/p.

insert into my_salgrade (grade) values(5);
this stmt should not insert the new row as per the logic,but the record is geting inserted.!!!!!

now what could be the mistake in the script.....????
-------------------------------------
create or replace trigger my_trig1
before insert on my_salgrade
for each row

declare
grade number(5):=0;

begin
if grade in (1,2,3,4,5) then
dbms_output.put_line('value already exists');
end if;
end;
-------------------------------------
Re: I got a problem while writing a simple procedure (split from hijacked thread by bb) [message #550682 is a reply to message #550681] Wed, 11 April 2012 09:24 Go to previous messageGo to next message
cookiemonster
Messages: 10930
Registered: September 2008
Location: Rainy Manchester
Senior Member
What makes you think that trigger will prevent an insert? There is nothing there to do that.
And you're still checking the value of a local variable instead of the value of the column. See Barbara's post.
I suggest you read the documentation on triggers.
Re: I got a problem while writing a simple procedure (split from hijacked thread by bb) [message #550684 is a reply to message #550682] Wed, 11 April 2012 09:26 Go to previous messageGo to next message
cookiemonster
Messages: 10930
Registered: September 2008
Location: Rainy Manchester
Senior Member
And can you please read and follow How to use [code] tags and make your code easier to read?
Re: I got a problem while writing a simple procedure (split from hijacked thread by bb) [message #550695 is a reply to message #550681] Wed, 11 April 2012 12:01 Go to previous message
Barbara Boehmer
Messages: 7975
Registered: November 2002
Location: California, USA
Senior Member
What your trigger does is check whether the variable grade with value 0 is equal to 1 or 2 or 3 or 4 or 5, then displays a message that the value already exists. It does not compare the inserted value to anything or have any effect on the insert.

As previously stated, you need to reference newly inserted values as :new.column_name within triggers. So, you should be comparing :new.grade not grade.

It is unclear what you are trying to do. Part of your code suggests that you are attempting to check that the grade is in a valid range. Your 'value already exists' suggests that you are checking for duplicates, which should be handled differently, such as using a unique key. It is also a completely different trigger and different table than your original post.
Previous Topic: Overriding the pre-defined Functions in oracle
Next Topic: DECODE and SUM query
Goto Forum:
  


Current Time: Tue Sep 02 18:08:58 CDT 2014

Total time taken to generate the page: 0.12031 seconds