Home » SQL & PL/SQL » SQL & PL/SQL » why the corelation identifier :new not allowing in after insert trigger
why the corelation identifier :new not allowing in after insert trigger [message #231442] Tue, 17 April 2007 02:13 Go to next message
iitraghu
Messages: 18
Registered: April 2007
Location: bengalore
Junior Member

create or replace trigger bef_ins_on_t
before insert on t
for each row
begin
:new.a := :new.a+10000;
end;

Trigger created.

insert into t values(0);
1 row created.

select * from t;

A
----------
10000
=========================================
create or replace trigger aft_ins_on_t
after insert on t
for each row
begin
:new.a := :new.a+10000;
end;

create or replace trigger aft_ins_on_t
*
ERROR at line 1:
ORA-04084: cannot change NEW values for this trigger type
Re: why the corelation identifier :new not allowing in after insert trigger [message #231444 is a reply to message #231442] Tue, 17 April 2007 02:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Because you are AFTER the insert and so you can't modify the values as they are already inserted.

Regards
Michel


Re: why the corelation identifier :new not allowing in after insert trigger [message #231447 is a reply to message #231444] Tue, 17 April 2007 02:21 Go to previous messageGo to next message
iitraghu
Messages: 18
Registered: April 2007
Location: bengalore
Junior Member

If that is the case,

i created two triggers on my emp table,
those are
1) before delete on emp
2) after delete on emp

in these two triggers i used the :OLD corelated identifier,
Both triggers are working perfectly.
then how can u capture the :old if the row was already deleted.
Re: why the corelation identifier :new not allowing in after insert trigger [message #231450 is a reply to message #231447] Tue, 17 April 2007 02:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Values are in memory.
You can read them.
You can't modify them.

Regards
Michel
Re: why the corelation identifier :new not allowing in after insert trigger [message #231452 is a reply to message #231450] Tue, 17 April 2007 02:35 Go to previous messageGo to next message
iitraghu
Messages: 18
Registered: April 2007
Location: bengalore
Junior Member

No,
I also modified them ,

the below trigger adds 10000 values to sal field before deletion
of row and inserted that row to emp_backup table

create or replace trigger bef_del_on_emp
before delete on emp
for each row
begin
insert into emp_backup (empno,ename,job,mgr,hiredate,sal,comm,deptno)
values
(:old.empno,:old.ename,:old.job,:old.mgr,
:old.hiredate,:old.sal+10000,:old.comm,:old.deptno);
end;

trigger created sucsessfully and inserted the row into the
emp_backup table with increasing the sal field by 10000
==========================================================
the below trigger adds 10000 values to sal field after deletion
of row and inserted that row to emp_backup table

create or replace trigger aftef_del_on_emp
after delete on emp
for each row
begin
insert into emp_backup (empno,ename,job,mgr,hiredate,sal,comm,deptno)
values
(:old.empno,:old.ename,:old.job,:old.mgr,
:old.hiredate,:old.sal+10000,:old.comm,:old.deptno);
end;

Re: why the corelation identifier :new not allowing in after insert trigger [message #231453 is a reply to message #231452] Tue, 17 April 2007 02:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't see in your code where you modify the :old fields.

Regards
Michel
Re: why the corelation identifier :new not allowing in after insert trigger [message #231455 is a reply to message #231453] Tue, 17 April 2007 02:57 Go to previous messageGo to next message
iitraghu
Messages: 18
Registered: April 2007
Location: bengalore
Junior Member

last but one line
Re: why the corelation identifier :new not allowing in after insert trigger [message #231457 is a reply to message #231455] Tue, 17 April 2007 03:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You don't modify the field you just add 10000 to its value, the value of the field remains the same.

Regards
Michel
Re: why the corelation identifier :new not allowing in after insert trigger [message #231464 is a reply to message #231455] Tue, 17 April 2007 03:39 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
old values have no meaning in an insert trigger. There is no old row when you insert.
Why do you want to split the two things in two separate triggers?

I think you should read up on triggers in the documentation. It looks like you missed some of the basics.
Re: why the corelation identifier :new not allowing in after insert trigger [message #231469 is a reply to message #231442] Tue, 17 April 2007 03:47 Go to previous message
iitraghu
Messages: 18
Registered: April 2007
Location: bengalore
Junior Member

sorry,
i got it.
Previous Topic: Oracle table to xls file
Next Topic: how to avoid control characters in the data
Goto Forum:
  


Current Time: Fri Dec 09 05:59:25 CST 2016

Total time taken to generate the page: 0.06897 seconds