Home » SQL & PL/SQL » SQL & PL/SQL » please correct this trigger
please correct this trigger [message #396894] Wed, 08 April 2009 06:00 Go to next message
sekharsomu
Messages: 69
Registered: December 2008
Member
Create or replace trigger ranger
  before insert on emp
    for each row
   begin
  
if new.empno>5000
   then
    raise_application_error(-20400,'greater than expected');
  end if;
end;
/


error
asking for declaration of new.empno
Re: please correct this trigger [message #396895 is a reply to message #396894] Wed, 08 April 2009 06:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
:new

Regards
Michel
Re: please correct this trigger [message #396896 is a reply to message #396894] Wed, 08 April 2009 06:03 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Use :new

By
Vamsi
Re: please correct this trigger [message #396897 is a reply to message #396894] Wed, 08 April 2009 06:04 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
"new." should have a colon (:) sign in front of it:
if :new.empno > 5000 then
   ...
Re: please correct this trigger [message #396899 is a reply to message #396894] Wed, 08 April 2009 06:05 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Almost there!
Try the documentation
Re: please correct this trigger [message #396901 is a reply to message #396899] Wed, 08 April 2009 06:10 Go to previous messageGo to next message
sekharsomu
Messages: 69
Registered: December 2008
Member
THANX FOR EVERY ONE WHO HELPED
I HAD BEEN I WHILE I TOUCHED THE PC DUE TO ILL HEALTH
SO A COMMON MISTAKE OF FORGETTING THE SYNTAX
Re: please correct this trigger [message #396903 is a reply to message #396901] Wed, 08 April 2009 06:17 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Don't use Caps, unless it is necessary.
Read the Forum Guide.

By
Vamsi
Re: please correct this trigger [message #396904 is a reply to message #396901] Wed, 08 April 2009 06:17 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Please do not post in upper case as it implies that you are shouting (generally regarded to be rude) Thanks
Re: please correct this trigger [message #396986 is a reply to message #396904] Wed, 08 April 2009 10:01 Go to previous messageGo to next message
user2004
Messages: 33
Registered: April 2009
Member
Instead of creating trigger its better to have check contraint on the table. Am I correct?


Thanks
Re: please correct this trigger [message #396987 is a reply to message #396986] Wed, 08 April 2009 10:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Correct.

Regards
Michel
Re: please correct this trigger [message #396989 is a reply to message #396987] Wed, 08 April 2009 10:11 Go to previous messageGo to next message
user2004
Messages: 33
Registered: April 2009
Member
Thanks for the quick response
Re: please correct this trigger [message #396991 is a reply to message #396986] Wed, 08 April 2009 10:12 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
user2004 wrote on Wed, 08 April 2009 17:01
Instead of creating trigger its better to have check contraint on the table. Am I correct?


In this exact case yes.

Otherwise it would depend on the check. If the check that has to be done is somewhat "dynamic", then a check constraint can't be used.

For example you could use a trigger to prevent inserts of dates greater than sysdate and smaller than sysdate - 20 in a transaction table, which can't be done with a check constraint because sysdate is not deterministic.
Re: please correct this trigger [message #397000 is a reply to message #396991] Wed, 08 April 2009 10:30 Go to previous message
user2004
Messages: 33
Registered: April 2009
Member
Thanks for the update.
Previous Topic: refresh a materialized view
Next Topic: UTL_FILE
Goto Forum:
  


Current Time: Sun Dec 11 02:04:19 CST 2016

Total time taken to generate the page: 0.08619 seconds