Home » SQL & PL/SQL » SQL & PL/SQL » Triggers
Triggers [message #37463] Sun, 10 February 2002 01:43 Go to next message
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 Go to previous message
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;
Previous Topic: Re: triggers
Next Topic: ORDER BY
Goto Forum:
  


Current Time: Sun May 31 03:17:40 CDT 2020