Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 556 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 468 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
Viewing before and after trigger change

Yoni Sade
Member Posts: 36
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 ?
Can someone tell me what's the SQLPLUS syntax to enable this output ?
Answers
-
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] >
-
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 -
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 -
Hi,
Do you mean with a Query... !!
- Pavan Kumar N -
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.