|Triggers [message #37463]
||Sun, 10 February 2002 01:43
Registered: October 2000
Hi, I would like to know how to capture the column value in where clause of an update statement.|
For example, we have two tables, EMP and EMP_DUPLICATE.
EMP_DUPLICATE is replica of EMP, same columns and same values. I have create a trigger on table EMP which is after update trigger which updates the EMP_DUPLICATE TABLE to keep in sync in table EMP, the trigger looks like
CREATE OR REPLACE TRIGGER update_emp
FOR EACH ROW
Set ENAME = :NEW.ENAME
Where EMPNO = :NEW.EMPNO;
And when I issue an Update statement against EMP which looks like
Update EMP Set ENAME = 'James' Where EMPNO = 7777
The trigger updates the EMP_DUPLICATE correctly, i.e., name of employee with EMPNO 7777 changes in EMP_DUPLICATE also.
But when I issue an Update statement like the Following :
Update EMP Set ENAME = 'James'
all the rows in EMP table get updated, that is OK, because we don't have any where clause, but what is happening is all the rows in EMP_DUPLICATES are also getting Updated even though there is a where clause in the Update statement of the trigger body.
Can one explain me why this behaviour and maybe a solution.
I want to capture the value of column in the where clause of the UPDATE statement and use that in the where clause of UPDATE statement in the trigger.
Thanks in Advance.