Home » SQL & PL/SQL » SQL & PL/SQL » Triggers: how do I access values passed into a trigger?
Triggers: how do I access values passed into a trigger? [message #285505] Tue, 04 December 2007 22:48 Go to next message
leif
Messages: 2
Registered: December 2007
Junior Member
Ok, I've been struggling to find out how to do this for most of the day and haven't had any luck. Any help is much appreciated!

I'm learning triggers and I would like to do a basic select on the value (partno) being inserted to get the current number of quotes for that part number. From everything I read this is the approach I should take.... but it doesn't work I get the following error. ORA-04082: NEW or OLD references not allowed in table level triggers Is it possible to do what I'm trying to do? Thanks for the help!


CREATE OR REPLACE trigger TR_min_quotes
after insert or delete on tbQuote
declare
x number;
begin

select NumberofQuotes into x
from PartQuoteCounts where partno = :new.partno;
if x < 2 then
dbms_output.put_line (
'*** Rule Violation ***');
end if;
end TR_min_quotes;
/
Re: Triggers: how do I access values passed into a trigger? [message #285508 is a reply to message #285505] Tue, 04 December 2007 23:01 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Quote:

ORA-04082: NEW or OLD references not allowed in table level triggers


Error itself is clear. NEW or OLD reference are not allowed in table(statement ) level trigger .


And how will you refer :new values in case of delete dml statement? (Use WHEN statement)

It is allowed only in row level trigger Neutral

Search for Row level trigger and Infamous Mutating Errors

Thumbs Up
Rajuvan.

[Updated on: Tue, 04 December 2007 23:03]

Report message to a moderator

Re: Triggers: how do I access values passed into a trigger? [message #285509 is a reply to message #285508] Tue, 04 December 2007 23:04 Go to previous messageGo to next message
leif
Messages: 2
Registered: December 2007
Junior Member
Thanks, right after I posted I found that I was missing for each row...

now, like you said I am getting the table TBQUOTE is mutating, trigger/function may not see it error....
Re: Triggers: how do I access values passed into a trigger? [message #285511 is a reply to message #285505] Tue, 04 December 2007 23:21 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
http://tahiti.oracle.com
Go find & read the Developer's Guide

coding by trail & error can be a VERY frustrating experience.

http://asktom.oracle.com
contains many fine coding examples.

Re: Triggers: how do I access values passed into a trigger? [message #285512 is a reply to message #285505] Tue, 04 December 2007 23:21 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

As i suggested ,

Go on search .. for knowing how it happens , and method to prevent this .

Thumbs Up
Rajuvan
Re: Triggers: how do I access values passed into a trigger? [message #285515 is a reply to message #285505] Tue, 04 December 2007 23:29 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

This will be a good start

Thumbs Up
Rajuvan
Re: Triggers: how do I access values passed into a trigger? [message #285541 is a reply to message #285509] Wed, 05 December 2007 00:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Always post your Oracle version (4 decimals).

The code you posted can't generate a mutating table error.
Post the actual code.

Regards
Michel
Re: Triggers: how do I access values passed into a trigger? [message #285613 is a reply to message #285541] Wed, 05 December 2007 02:52 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
As an additional advice: Don't use CamelCase naming in SQL. Since all objectnames are converted to all-caps, this makes objectnames unreadable.
The convention in (Oracle) SQL is to use underscores as word-separators
Previous Topic: Cusor and if statement
Next Topic: DBMS_CRYPTO IN ORACLE 10G
Goto Forum:
  


Current Time: Fri Dec 02 14:22:09 CST 2016

Total time taken to generate the page: 0.13657 seconds