Forum Stats

  • 3,837,675 Users
  • 2,262,284 Discussions
  • 7,900,351 Comments

Discussions

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 ?

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] > 
  • 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
  • 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
  • Pavan Kumar
    Pavan Kumar Member Posts: 11,904 Gold Crown
    Hi,

    Do you mean with a Query... !!

    - Pavan Kumar N
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,580 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.
This discussion has been closed.