Home » SQL & PL/SQL » SQL & PL/SQL » Confusions regarding the code
Confusions regarding the code [message #352973] Fri, 10 October 2008 06:44 Go to next message
SUHAS22845
Messages: 51
Registered: August 2008
Location: BANGALORE
Member

Hi all,

I have written a trigger code,

CREATE OR REPLACE TRIGGER sg_employee_trig
   BEFORE INSERT
   ON sg_employee
   FOR EACH ROW
BEGIN
   IF INSERTING
   THEN
      IF NEW.salary > 1500
      THEN
         DBMS_OUTPUT.put_line ('SALARY CANNOT BE MORE THAN 1500');
      ELSE
         DBMS_OUTPUT.put_line ('INSERTION SUCCESSFULL');
      END IF;
   END IF;
END;


but this is firing a error saying invalid identifier
--Salary must be declared.

Can anyone please help me out on this.
Re: Confusions regarding the code [message #352974 is a reply to message #352973] Fri, 10 October 2008 06:45 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Use :NEW.salary not NEW.salary
Re: Confusions regarding the code [message #352976 is a reply to message #352973] Fri, 10 October 2008 06:49 Go to previous messageGo to next message
podzach
Messages: 6
Registered: October 2008
Location: Sioux Falls, SD
Junior Member
Next time please post the version of DB you are on. If this is an Oracle trigger, then you need a : before the word new in new.salary.
Re: Confusions regarding the code [message #352977 is a reply to message #352974] Fri, 10 October 2008 06:50 Go to previous messageGo to next message
SUHAS22845
Messages: 51
Registered: August 2008
Location: BANGALORE
Member

Hi ,

I tried using :New.salary as well, the trigger is gettin created but, when i insert a salary >1500, the rows are gettin inserted. Sad
Do i have to change the logic ?
Re: Confusions regarding the code [message #352978 is a reply to message #352976] Fri, 10 October 2008 06:56 Go to previous messageGo to next message
SUHAS22845
Messages: 51
Registered: August 2008
Location: BANGALORE
Member

Sorry for not mentioning the DB, am using oracle 10g.

I tried with a :NEW as well, the trigger gets created, but its not restricting me from inserting a value greater than 1500.. Sad
Re: Confusions regarding the code [message #352979 is a reply to message #352978] Fri, 10 October 2008 06:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Prove it, copy and paste your SQL*Plus session.

Regards
Michel
Re: Confusions regarding the code [message #352986 is a reply to message #352979] Fri, 10 October 2008 07:08 Go to previous messageGo to next message
SUHAS22845
Messages: 51
Registered: August 2008
Location: BANGALORE
Member

Hi, Thanks a lot guys,

The Trigger was workin fine,

instead of dbms_output.put_line,
I had to use raise_application_error.

thanks a lot guys.

regards
suhas
Re: Confusions regarding the code [message #352998 is a reply to message #352986] Fri, 10 October 2008 08:19 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Just so you know, using DBMS_OUTPUT in a trigger is pretty useless. Unless you have SERVEROUTPUT ON and you are running the INSERT in a local SQL*Plus session, no one is every going to see the messages (ie. Forms, Application, Procedures, etc.)
Previous Topic: not Load PL/SQL in PROC
Next Topic: 2 separate select works but when union all gives error
Goto Forum:
  


Current Time: Mon Dec 05 09:03:51 CST 2016

Total time taken to generate the page: 0.08575 seconds