Home » SQL & PL/SQL » SQL & PL/SQL » Error in creating a trigger (Oracle10g(10.2.0.4.0),windows xp)
Error in creating a trigger [message #465416] Wed, 14 July 2010 02:03 Go to next message
piya_gupta09
Messages: 15
Registered: July 2010
Location: KANPUR
Junior Member
hello am facing this problem while creating the trigger.....

set serveroutput on
create or replace trigger Before_Insert_Trigger
before insert on teacher
begin
       dbms_output.put_line(:new.f_name);
end;

create or replace trigger Before_Insert_Trigger
                          *
ERROR at line 1:
ORA-04082: NEW or OLD references not allowed in table level triggers


why i am facing this error?? kindly help





Re: Error in creating a trigger [message #465418 is a reply to message #465416] Wed, 14 July 2010 02:04 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
ORA-04082: NEW or OLD references not allowed in table level triggers 
Cause: The trigger is accessing "new" or "old" values in a table trigger.
 
Action: Remove any new or old references.
 


in your case
dbms_output.put_line(:new.f_name);

[Updated on: Wed, 14 July 2010 02:06]

Report message to a moderator

Re: Error in creating a trigger [message #465421 is a reply to message #465418] Wed, 14 July 2010 02:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68763
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
:NEW and :OLD can only be used in row level trigger (FOR EACH ROW option).

Regards
Michel
Re: Error in creating a trigger [message #465422 is a reply to message #465418] Wed, 14 July 2010 02:15 Go to previous messageGo to next message
piya_gupta09
Messages: 15
Registered: July 2010
Location: KANPUR
Junior Member
@rahulvb

but how would i be able to print the new first name if i want to print it before insert it.shouldn't the trigger be able to access the new value through :new?.f_name????
Re: Error in creating a trigger [message #465423 is a reply to message #465416] Wed, 14 July 2010 02:16 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Row level triggers (FOR EACH ROW is specified in the declaration of the trigger) fire once for each inserted row.
Statement level triggers fire once for each insert statement.
Now, since an insert statement can result in more than one row being inserted (e.g. insert ... select * from ...), you cannot point to a single :new.xxx in a statement level trigger: Which value of :new.xxx should be used? (which row?)
Re: Error in creating a trigger [message #465425 is a reply to message #465422] Wed, 14 July 2010 02:17 Go to previous messageGo to next message
piya_gupta09
Messages: 15
Registered: July 2010
Location: KANPUR
Junior Member
@michel
hey thanks just resolved it Smile thank you
Re: Error in creating a trigger [message #465427 is a reply to message #465425] Wed, 14 July 2010 02:18 Go to previous messageGo to next message
piya_gupta09
Messages: 15
Registered: July 2010
Location: KANPUR
Junior Member
@frank

good explanation will keep in mind thanks
Re: Error in creating a trigger [message #465429 is a reply to message #465427] Wed, 14 July 2010 02:27 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
and for future reference Oracle PL/SQL Tutorial

[Updated on: Wed, 14 July 2010 02:30]

Report message to a moderator

Re: Error in creating a trigger [message #465506 is a reply to message #465416] Wed, 14 July 2010 07:50 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
But what is the point of a DBMS_OUTPUT anyway in a trigger. An application inserting the row will will not see any output from a trigger.

[mod - typo fixed]

[Updated on: Wed, 14 July 2010 07:51]

Report message to a moderator

Re: Error in creating a trigger [message #465515 is a reply to message #465506] Wed, 14 July 2010 08:36 Go to previous message
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
Senior Member
joy_division wrote on Wed, 14 July 2010 08:50
But what is the point of a DBMS_OUTPUT anyway in a trigger. An application inserting the row will will not see any output from a trigger.


Why not? DBMS_OUTPUT.PUT_LINE(S) is paired with DBMS_OUTPUT.GET_LINE(S) Smile. Question is why would you use such exotic way of communication (unless you are debugging trigger)?

SY.

[Updated on: Wed, 14 July 2010 08:37]

Report message to a moderator

Previous Topic: query to select the required translators
Next Topic: Column renaming from Query output
Goto Forum:
  


Current Time: Fri Jul 25 15:06:28 CDT 2025