Re: How to identify updated field in a trigger

From: <fitzjarrell_at_cox.net>
Date: 21 Sep 2006 13:10:01 -0700
Message-ID: <1158869401.923322.134060@m73g2000cwd.googlegroups.com>

frank.van.bortel_at_gmail.com wrote:
> Mando schreef:
>
> > Hi
> >
> > Does anyone know whether a trigger can identify which field has been
> > updated in a table? I need to write an after update trigger which
> > passes the new value from the updated row, but I need to just get the
> > field that's been updated.
> >
> > e.g. table T1 with 3 rows: 1,2,3 and 3 columns: a,b,c
> >
> > and table T2 with 3 rows: 1,2,3 and 3 columns: a,b,c.
> >
> > If T1: row1,column b gets updated I need to get that specific value.
> >
> > Any help much appreciated.
> >
>
> Whoops... missed the 'after update' bit- forget :old and :new.

Why forget about it?

SQL> create trigger beforeaftertest
  2 after update on emp
  3 for each row
  4 begin

  5     dbms_output.put_line('Old ename: '||:old.ename)
  6     dbms_output.put_line('New ename: '||:new.ename)

  7 end;
  8 /

Trigger created.

SQL> set serveroutput on size 1000000
SQL> update emp
  2

SQL>
SQL>
SQL> select ename

  2 from emp;

ENAME

---

SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS ENAME

---

JAMES
FORD
MILLER 14 rows selected.

SQL> update emp
  2 set ename = initcap(ename);

Old ename: SMITH
New ename: Smith
Old ename: ALLEN
New ename: Allen
Old ename: WARD
New ename: Ward
Old ename: JONES
New ename: Jones
Old ename: MARTIN
New ename: Martin
Old ename: BLAKE
New ename: Blake
Old ename: CLARK
New ename: Clark
Old ename: SCOTT
New ename: Scott
Old ename: KING
New ename: King
Old ename: TURNER
New ename: Turner
Old ename: ADAMS
New ename: Adams
Old ename: JAMES
New ename: James
Old ename: FORD
New ename: Ford
Old ename: MILLER
New ename: Miller

14 rows updated.

SQL> It appears :old and :new work just fine in an after update trigger.

> The after update of [column] would bring a possible solution

David Fitzjarrell Received on Thu Sep 21 2006 - 15:10:01 CDT