Home » SQL & PL/SQL » SQL & PL/SQL » Inserting and Deleting (Windows XP DB 9i - forms 6i)
Inserting and Deleting [message #377329] Mon, 22 December 2008 07:48 Go to next message
*munnabhai*
Messages: 157
Registered: March 2008
Location: Riyadh
Senior Member
Hi guyz,

iam new in this SQL forum i have one query hope i will get the response from seniors

i need update database trigger. i have three tables as below.

1) emp
empid
deptid
emp_name
emp_status
work_location
join_date
left_date

2) Termination
empid
deptid
emp_name
work_location
t_date

3) Resignation
empid
deptid
emp_name
work_location
r_date

i have list item on emp.emp_status contain Termination and Resignation

if user select the Termination in employee form trigger should update table emp as well termination table and insert the current values from emp to Termination/Resignatino and delete the data belongs to Termination/Resignation from emp.

below code is working and inserting data into termination table but not deleting records from emp how can i delete the record from emp?

create or replace trigger on_resignation after update on emp for each row
begin

if :new.emp_status='Termination' then
insert into termination(empid,deptid,emp_name,work_location,t_date)values
(:new.empid,:new.deptid,:new.emp_name,:new.work_location,sysdate);
elsif
:new.emp_status='Resignation' then
insert into Resignation(empid,deptid,emp_name,work_location,r_date)values
(:new.empid,:new.deptid,:new.emp_name,:new.work_location,sysdate);
end if;
end;

even i used the code after inserting records into another table

delete from emp where emp_status='Resignation';

if i add this line it gives me below error.

ORA-04091: table TEST1.EMP is mutating, trigger/function may not see it
ORA-06512: at "TEST1.ON_RESIGNATION", line 5
ORA-04088: error during execution of trigger 'TEST1.ON_RESIGNATION'


how can i perform this task anyone help me plz?


Waiting for replies.

Regards
Re: Inserting and Deleting [message #377332 is a reply to message #377329] Mon, 22 December 2008 07:56 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Check these link for more information.

http://www.oracle-base.com/articles/9i/MutatingTableExceptions.php

http://asktom.oracle.com/tkyte/Mutate/

I didn't realise you had more than 130+ posts and still you are bit reluctant to follow the forum guidelines. If could you please spend some time in reading the forum guidelines. It will be much appreciated.

Regards

Raj

[Updated on: Mon, 22 December 2008 07:57]

Report message to a moderator

Re: Inserting and Deleting [message #377333 is a reply to message #377332] Mon, 22 December 2008 07:58 Go to previous messageGo to next message
*munnabhai*
Messages: 157
Registered: March 2008
Location: Riyadh
Senior Member
Dear Rajaram,

thanks for your link for your information already i search the forum but i didnt find suitable query thats why i post they question.

Regards
Re: Inserting and Deleting [message #377334 is a reply to message #377333] Mon, 22 December 2008 08:00 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Are you sure you did all these
Quote:

Before you decide to open a new topic, you should ask yourself:

First of all: did I try myself? Or am I just hoping that someone else is willing to do my work?

Have I checked the Wiki page (this site's knowledge base)?

Did I search the board properly? Have I also tried the Site Search Engine?
A large group of questions posted here are just a déja -répondu of the past.

Did I read the documentation? A lot of questions are answered there. Really.

Did I use Google? Even if your question hasn't been answered here, chances are that on other websites someone already has posted an answer.

Is my question specific to the Oracle database? Now, that may seem obvious since this site is called Oracle FAQs, but we get quite a few questions about Access and MS SQL Server here - and SQL between databases is not always compatible - so please ask any non-Oracle questions elsewhere. It will be to your advantage.
Ok, so you didn't find an answer yet. No sweat, you're welcome to post it here and a lot of people are happy to answer. They can, however, answer only if enough info is provided.



Especially this one

Quote:

Did I use Google? Even if your question hasn't been answered here, chances are that on other websites someone already has posted an answer.


Because I used the same search engine to get these links.

Regards

Raj
Re: Inserting and Deleting [message #377338 is a reply to message #377334] Mon, 22 December 2008 08:02 Go to previous messageGo to next message
*munnabhai*
Messages: 157
Registered: March 2008
Location: Riyadh
Senior Member
Yes im sure.

Yes the second link i got from google only,

anyhow rajaram thanks for you help Smile i think the first link could help me Laughing



Regards

[Updated on: Mon, 22 December 2008 08:05]

Report message to a moderator

Re: Inserting and Deleting [message #377341 is a reply to message #377338] Mon, 22 December 2008 08:05 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:

thanks for your link for your information already i search the forum but i didnt find suitable query thats why i post they question.


Quote:

Yes the second link i got from google only,


If you got the asktom link from google, then you didn't explain why it didn't fit your requirement ?

Regards

Raj
Previous Topic: Executing stored procedure
Next Topic: Distributed Names
Goto Forum:
  


Current Time: Wed Dec 07 18:56:15 CST 2016

Total time taken to generate the page: 0.06378 seconds