Oracle Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Dev Shed ForumsDatabasesOracle Development

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
  #1  
Old May 22nd, 2005, 03:07 PM
riki riki is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: May 2005
Posts: 1 riki User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 12 m 19 sec
Reputation Power: 0
AFTER UPDATE trigger help

I am having problems creating an AFTER UPDATE trigger to display newly updated salaries from an employee table I have. I am not too sure of how to display the salaries that were updated in the trigger. The code I used to create the trigger is below:

CREATE TRIGGER incr_sal
AFTER UPDATE ON emp_table
FOR EACH ROW
DECLARE
v_sal emp_table.salary%TYPE;
BEGIN
SELECT salary
INTO v_sal
FROM emp_table
WHERE salary = :new.salary;
END incr_sal;

After creating the trigger, I tried to update emp_table by using:
DECLARE
v_increase
BEGIN
UPDATE emp_table
SET salary = salary + (salary * .05)
END;

But I keep getting errors:
Error at line 1:ORA-04091: table S305AD5.COLLINSK_TABLE is mutating, trigger/function may not see it

ORA-06512: at "S305AD5.INCR_SAL", line 4ORA-04088: error during execution of trigger 'S305AD5.INCR_SAL'

Could anyone please help? I am very confused

I just want to have my updates be displayed after O update them.

Reply With Quote
  #2  
Old May 23rd, 2005, 05:56 PM
wubandit99 wubandit99 is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Jan 2005
Posts: 30 wubandit99 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 8 h 6 m 31 sec
Reputation Power: 4
Your SELECT statement in the trigger is what is causing the problem. You can get around this using IF logic on the before and after values of the field:

Code:
CREATE OR REPLACE TRIGGER incr_sal
AFTER UPDATE ON emp_table
FOR EACH ROW
DECLARE
BEGIN
	IF :new.salary <> :old.salary
	  OR :new.salary is null and :old.salary is not null
	  OR :new.salary is not null and :old.salary is null
	THEN
		insert into salary_changes
		(
		  emp_id, old_salary, new_salary
		)
		values
		(
		  :new.emp_id, :old.salary, :new.salary
		);
	END IF;
END incr_sal;

Last edited by wubandit99 : May 23rd, 2005 at 05:57 PM. Reason: put in code tags

Reply With Quote
Reply

Viewing: Dev Shed ForumsDatabasesOracle Development > AFTER UPDATE trigger help


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 2 hosted by Hostway