|
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
#1
|
|||
|
|||
|
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. |
|
#2
|
|||
|
|||
|
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 |
![]() |
| Viewing: Dev Shed Forums > Databases > Oracle Development > AFTER UPDATE trigger help |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|