Forum Stats

  • 3,838,625 Users
  • 2,262,385 Discussions
  • 7,900,710 Comments

Discussions

Categories

Viewing before and after trigger change

Yoni Sade
Yoni Sade Member Posts: 36
edited Sep 21, 2008 4:06PM in SQL & PL/SQL
I remember there is a way to show in SQLPLUS the before and after image of an UPDATE statement going through a trigger.
Can someone tell me what's the SQLPLUS syntax to enable this output ?
Tagged:
· Share on TwitterShare on Facebook

Answers

  • Paul M.
    Paul M. Member Posts: 10,947 Gold Trophy
    Maybe you mean this ? 
    [email protected] > select sal from emp where empno=7902;

       SAL
----------
      3000

[email protected] > create or replace trigger emp_update
  2  before update on emp
  3  for each row
  4  begin
  5     if :new.sal != :old.sal then
  6             dbms_output.put_line('Old sal : '||:old.sal);
  7             dbms_output.put_line('New sal : '||:new.sal);
  8     end if;
  9  end;
 10  /

Trigger created.

[email protected] > update emp set sal=3100 where empno=7902;
Old sal : 3000
New sal : 3100

1 row updated.

[email protected] >
    · Share on TwitterShare on Facebook
  • Pavan Kumar
    Pavan Kumar Member Posts: 11,904 Gold Crown
    Hi,

    Here goes famous Tom Kyte Example .... 
    SQL> create table t ( x int, y int );

Table created.

SQL>  insert into t values ( 1, 1 );

1 row created.

SQL>  create or replace trigger t_bufer
  2   before update on t for each row
  3    begin
  4            dbms_output.put_line
  5            ( 'old.x = ' || :old.x ||
  6              ', old.y = ' || :old.y );
  7            dbms_output.put_line
  8            ( 'new.x = ' || :new.x ||
  9             ', new.y = ' || :new.y );
 10   end;
 11   /

Trigger created.

SQL> set serveroutput on

SQL> update t set x = x+1;
old.x = 1, old.y = 1
new.x = 2, new.y = 1

1 row updated.

SQL>
    - Pavan Kumar N
    · Share on TwitterShare on Facebook
  • Yoni Sade
    Yoni Sade Member Posts: 36
    Thanks,
    but in the past someone showed my a way to show the values without any special code in the trigger itself.
    Some SQL*PLUS syntax I presume.

    Yoni
    · Share on TwitterShare on Facebook
  • Pavan Kumar
    Pavan Kumar Member Posts: 11,904 Gold Crown
    Hi,

    Do you mean with a Query... !!

    - Pavan Kumar N
    · Share on TwitterShare on Facebook
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,598 Red Diamond
    Are you talking about flashback queries:
    SQL> set time on
16:06:34 SQL> select empno from emp where ename = 'KING';

     EMPNO
----------
      7839

16:06:49 SQL> update emp set ename = 'XYZ' where ename = 'KING';

1 row updated.

16:06:55 SQL> select ename from emp where empno = 7839;

ENAME
----------
XYZ

16:07:12 SQL> select ename from emp  as of timestamp(timestamp '2008-9-21 16:06:49')
16:07:36   2  where empno = 7839;

ENAME
----------
KING

16:07:42 SQL>
    SY.
    · Share on TwitterShare on Facebook
This discussion has been closed.