:NEW in cursor ? [message #424857] |
Tue, 06 October 2009 04:39  |
dietbeck
Messages: 6 Registered: October 2009
|
Junior Member |
|
|
Hi,
How can I create a cursor which contains the rows inserted in an "after insert" trigger ?
The :new does not seem to work or at least I get the error:
Error: ORA-04082: NEW or OLD references not allowed in table level triggers
In fact I am having joins in the statement thus I need values from the new rows combined with values from other tables which are joined by a foreign key
Thanks
dietbeck
[Updated on: Tue, 06 October 2009 04:46] Report message to a moderator
|
|
|
|
|
Re: :NEW in cursor ? [message #424936 is a reply to message #424857] |
Tue, 06 October 2009 11:12   |
dietbeck
Messages: 6 Registered: October 2009
|
Junior Member |
|
|
Hi,
ok here some code and an error. How do I reference the ":new" fields ?
Thanks
Dietbeck
cursor sci_cur is
select :new.vendor, :new.catalog, :new.cost, abbr, :new.size, unit, :new.quantity, :new.username, :new.updateusername, createusername
etc
OPEN sci_cur;
FETCH sci_cur into sci;
IF sci_cur%FOUND THEN
emailmessage := 'Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss')
|| crlf || 'From: <'|| sci.createusername ||'>'
|| crlf || 'To: '|| 'Recepient'
|| crlf || ''
|| crlf || 'Vendor: ' || crlf || sci.vendor
|| crlf || 'Catalog: ' || crlf || sci.catalog
|| crlf || 'Cost: ' || crlf || sci.cost || sci.abbr
|| crlf || 'Size: ' || crlf || sci.size || sci.unit
|| crlf || 'Quantity: ' || crlf || sci.quantity
|| crlf || 'Updater: ' || crlf || sci.updateusername;
Error(33,62): PLS-00302: component 'VENDOR' must be declared
|
|
|
|
Re: :NEW in cursor ? [message #424939 is a reply to message #424857] |
Tue, 06 October 2009 11:21   |
dietbeck
Messages: 6 Registered: October 2009
|
Junior Member |
|
|
Maybe I am not understanding, but "vendor" is a field from the select statement in the cursor which I want to later assign in the message text referenced through the cursor variable.
|
|
|
|
Re: :NEW in cursor ? [message #424949 is a reply to message #424857] |
Tue, 06 October 2009 11:46   |
cookiemonster
Messages: 13963 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
The :new and :old syntax is used exclusively by row level triggers to identify the values of the current row being modified. You can refer to them in such triggers but you can't refer to them anywhere else and selecting them in a cursor is basically meaningless.
So this:
CREATE OR REPLACE TRIGGER TABLE_TRIG
BEFORE INSERT ON <TABLE>
FOR EACH ROW
DECLARE
l_val NUMBER;
BEGIN
SELECT :NEW.col1 INTO l_val FROM <TABLE>
WHERE <some criteria>;
END;
Is logically equivalent to:
CREATE OR REPLACE TRIGGER TABLE_TRIG
BEFORE INSERT ON <TABLE>
FOR EACH ROW
DECLARE
l_col_val NUMBER := :new.col1;
l_val NUMBER;
BEGIN
SELECT l_col_val INTO l_val FROM <TABLE>
WHERE <some criteria>;
END;
And if you're not in a row level trigger that syntax won't compile at all.
Which is to say you can reference the new/old stuff without needing to select it, assuming you can reference it at all.
Why don't you tell us what you're trying to achieve here because whatever you're trying to do your current approach is almost certainly flawed.
Also when posting code can you please use code tags - see the orafaq forum guide if you don't know how.
|
|
|
Re: :NEW in cursor ? [message #424963 is a reply to message #424857] |
Tue, 06 October 2009 15:38   |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
if you are trying to send an email every time you enter a record then simply use a row level trigger and
emailmessage := 'Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss')
|| crlf || 'From: <'|| :new.createusername ||'>'
|| crlf || 'To: '|| 'Recepient'
|| crlf || ''
|| crlf || 'Vendor: ' || crlf || :new.vendor
|| crlf || 'Catalog: ' || crlf || :new.catalog
|| crlf || 'Cost: ' || crlf || :new.cost || :new.abbr
|| crlf || 'Size: ' || crlf || :new.size || :new.unit
|| crlf || 'Quantity: ' || crlf || :new.quantity
|| crlf || 'Updater: ' || crlf || :new.updateusername;
|
|
|
Re: :NEW in cursor ? [message #425257 is a reply to message #424963] |
Thu, 08 October 2009 05:28   |
dietbeck
Messages: 6 Registered: October 2009
|
Junior Member |
|
|
Hi,
I wanted to thank everyone for their help so far. I am trying to get better posting what is really required and using tags.
I had a row-level after insert trigger and it finally does work, extracting information from a table, including it in a message body and send it via email. Thats was what I wanted to achieve.
I have a remaining problem, namely with the following:
if :new.itemid is not null then
select distinct name_upper into cartitem_var.substance from cartitemname
where cartitemid = :new.itemid
and isdefault = 'Y'
and isactive = 'Y';
Exception message: ORA-20001: An error was encountered - 100 -ERROR- ORA-01403: no data found
ORA-06512: at "SUB.SENDEMAIL_TRG", line 73
ORA-04088: error during execution of trigger 'SUB.SENDEMAIL_TRG'
The problem is, I believe, that the select is on a table which is inserted at the same time as the table where the trigger is on. Thus I assume this is a "mutating" table and thus the data are not found, since they are inserted at the same time as the insert of the row where the trigger is fired. Is there any way I can get around this problem getting the value I need from the other table ?
Thanks
dietbeck
[Updated on: Thu, 08 October 2009 05:38] Report message to a moderator
|
|
|
|
|
|