Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2006
    Posts
    2

    Unanswered: ROWID in a TRIGGER

    Hi,

    I have an AFTER UPDATE TRIGGER but need to track each row which is updated.

    Following is the simple example:

    need to get the EMPNO whenever there is change in DEPTNO for an employee in EMP.

    CREATE OR REPLACE TRIGGER ed_chg
    AFTER UPDATE ON emp
    FOR EACH ROW
    begin
    if updating then
    select empno into v_empno from emp
    where deptno=:new.empno;
    dbms_output.put_line('The employee who had a change in Dept : '||v_empno);
    --the above one will give an error of too_many_rows. so need to track only that row which is updated.

    end if;
    end;

    Thanks in Advance,
    Nani

  2. #2
    Join Date
    Dec 2003
    Posts
    1,074
    Do you mean something more like this:

    Code:
    CREATE OR REPLACE TRIGGER ed_chg
    AFTER UPDATE ON emp
    FOR EACH ROW
    begin
    if updating then
    
      if :old.deptno <> :new.deptno then
        dbms_output.put_line('The employee who had a change in Dept : '||:new.empno);
      end if;
    
    end;

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Just as an FYI, you are NOT allowed to query the same table that the trigger is firing on. If you do, you get a mutating table error.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Feb 2006
    Posts
    2
    Thanks Chuck . That will do the job.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •