Home » SQL & PL/SQL » SQL & PL/SQL » Updating Records (Windows XP DB 8i - forms 6i)
Updating Records [message #378346] Tue, 30 December 2008 01:58 Go to next message
*munnabhai*
Messages: 157
Registered: March 2008
Location: Riyadh
Senior Member
Hi guyz,

I have a query, anyone give me the simple and easiest solution for the below.

I have three table one master detail and other for record history.

1) employee
empid varchar2(10)primary key,
emp_name varchar2(30),
location varchar2(30),
emp_status varchar2(10),
join_date date,
left_date date;

2) emp_picture
(idno varchar2(10)references employee;
emp_pic blob;

3) history
empid varchar2(10),
emp_name varchar2(30),
join_date date,
left_date date;

I'm using the below trigger for update.

create or replace trigger resign after update on employee
      for each row
  begin
      insert into resign
      (empid,
       emp_name
       join_date,
       left_date) values
     (:new.empid,
       :new.emp_name
       :new.join_date,
       :new.left_date);
--delete from employee where emp_status='Resign';
end;


If I remove the delete statement it works fine. but i want to delete the record depend on emp_status.

It gives me mutating table error i know that one is there any other simplest way to solve the above? or should i write complete package to resolve the mutating error?

or should i used ON DELETE CASCADE? in master details

Regards
Re: Updating Records [message #378358 is a reply to message #378346] Tue, 30 December 2008 02:41 Go to previous messageGo to next message
dhanuka.rajesh
Messages: 49
Registered: March 2008
Location: Mumbai
Member
You cannot perform, Select - Update - Delete - Insert on table for which trigger is being executed.
Try using Instead of triggers.

Best Regards,

Dhanuka. Rajesh
Re: Updating Records [message #378375 is a reply to message #378346] Tue, 30 December 2008 03:17 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
You can put the delete statement in a separate after update statement (not row) trigger.
Re: Updating Records [message #378376 is a reply to message #378346] Tue, 30 December 2008 03:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous post:
Michel Cadot wrote on Thu, 25 December 2008 09:12
From your previous post:
Michel Cadot wrote on Mon, 22 December 2008 15:02
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Always post your Oracle version (4 decimals).

Also always post a Test case: create table and insert statements along with the result you want with these data.

...

Regards
Michel

Re: Updating Records [message #378379 is a reply to message #378376] Tue, 30 December 2008 03:24 Go to previous messageGo to next message
*munnabhai*
Messages: 157
Registered: March 2008
Location: Riyadh
Senior Member
Sorry cadot i forget again. Sad

i will remeber next time. Cool
Re: Updating Records [message #378429 is a reply to message #378379] Tue, 30 December 2008 07:15 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
*munnabhai* wrote on Tue, 30 December 2008 10:24
Sorry cadot i forget again. Sad

i will remeber next time. Cool

No, you won't..
Previous Topic: pl/sql help
Next Topic: subtype INTEGER
Goto Forum:
  


Current Time: Fri Dec 09 15:58:54 CST 2016

Total time taken to generate the page: 0.32171 seconds