|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
|
|
#1
February 13th, 2005, 10:38 AM
|
|||
|
|||
|
trigger question, need help
here is my trigger:
Code:
create or replace trigger InsertintoCustomer After insert on customer for each row declare ID number; FN varchar2(10); LN varchar2(15); EM varchar2(40); begin FN:= :old.Fname; LN:= :old.Lname; Em:= FN||LN||'@gre.ac.uk'; insert into customer (Email) values (Em); end; / then i do this: Code:
insert into customer(Customer_id,fname,lname,street,city,postcode,phone,branch_no) values (trial.nextval,'shez','Azr','10 bloomfield','woolwich','se187jh','02056545454','01') i get an error saying: Code:
ORA-04091: table AS234.CUSTOMER is mutating, trigger/function may not see it ORA-06512: at "AS234.INSERTINTOCUSTOMER", line 11 ORA-04088: error during execution of trigger 'AS234.INSERTINTOCUSTOMER' btw: if anyone has seen a good online tutorial on Triggers, i would appreciate the url!
|
|
#2
February 14th, 2005, 10:24 AM
|
|||
|
|||
|
You have recursion. Your trigger is invoked on an insert. You call insert in the trigger itself, forcing the trigger to endlessly call itself.
I think you want to 'update' the newly inserted record, maybe something like this - Code:
new.Email :=Em;
|
|
#3
February 14th, 2005, 10:39 AM
|
||||
|
||||
|
Isn't this better accomplished with a view?
__________________
My blog Tutorials about OSS databases, DBMonster ... Contribute to OSS Development, fill bug reports! Developer Shed eSupport Commented my.ini/my.cnf (ADD YOUR OWN CONFIG TRICK) An introduction to database normalization Natural or Surrogate key Custom ordering for your results Correlated and uncorrelated subqueries Don't turn your outer joins into inner joins Random data (with a bias)
|
|
#4
February 14th, 2005, 10:52 AM
|
|||
|
|||
|
Pablo is saying you are needlessly duplicating data from another table - which is a design issue.
|
|
| Viewing: Dev Shed Forums > Databases > Oracle Development > trigger question, need help |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
