| Triggers [message #37463] |
Sun, 10 February 2002 01:43  |
Vinny
Messages: 2
Registered: October 2000
|
Junior Member |
|
|
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
AFTER UPDATE
ON EMP
FOR EACH ROW
Begin
Update EMP_DUPLICATE
Set ENAME = :NEW.ENAME
Where EMPNO = :NEW.EMPNO;
End;
/
-----
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.
|
|
|
|
| Re: Triggers [message #37465 is a reply to message #37463] |
Sun, 10 February 2002 22:58  |
Satish Shrikhande
Messages: 167
Registered: October 2001
|
Senior Member |
|
|
Update EMP_DUPLICATE
Set ENAME = :NEW.ENAME
Where EMPNO = :NEW.EMPNO;
change it with
Update EMP_DUPLICATE
Set ENAME = :NEW.ENAME
Where EMPNO = :old.EMPNO;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
