2

Any recommendations into how one can figure out which rows were solely modified from an existing row (edit) and which one were solely inserted?

I know minus can extract any rows that were modified in any way (add/delete/edit), but right now I feel the only way to differentiate an "edit" among these 3 is to compare "hard values" shared between corresponding records and detect the column change...

Take below for example:

Table GarageBefore
__________________________
|Make |Color |VIN |Driver |  
|AUDI |GRAY  |650 |SMITH  |
|BMW  |SILV  |808 |JOHNS  |
|BMW  |GREE  |754 |CARRO  |
|CHEV |WHIT  |532 |JONES  |
|_________________________|

Table GarageAfter
__________________________
|Make |Color |VIN |Driver |  
|AUDI |GRAY  |650 |SMITH  |
|BMW  |SILV  |303 |JOHNS  |
|BMW  |GREE  |754 |CARRO  |
|BUIC |BLAC  |400 |JAMES  |
|CHEV |WHIT  |532 |JONES  |
|_________________________|


Desired Result

(Modified Existing Rows?)
|BMW  |SILV  |303 |JOHNS  |


(Added Rows?)
|BUIC |BLAC  |400 |JAMES  |
2
  • Without some sort of immutable key, you can't. In this example, I'd expect that to be vin. A vehicle's VIN is never going to change, it's a unique, immutable natural key. But that's the one value that did change. You can guess by looking to see how many columns match, potentially assigning different priorities to different columns (i.e. a match on color is less definitive than a match on make which is less definitive than a match on driver). But that's going to be probabilistic and highly data dependent. Aug 13, 2015 at 5:45
  • Use triggers - or Oracle also have FGA (Fine Grained Auditing) which may do what you want - AFAIK, it's EE (Enterprise Edition).
    – Vérace
    Aug 13, 2015 at 8:11

2 Answers 2

1

From the datasets you posted, no. You could add a column "modified" which contains a flag that tells you whether the row was modified or not. That column could be maintained via a trigger on the table, or, in my opinion better, you could encapsulate inserts/updates to that table in a stored procedure and have the procedure maintain that column.

0
create table garage
(
  make varchar2(10),
  color varchar2(10),
  vin number,
  driver varchar2(10)
);

insert into garage values ('AUDI', 'GRAY', 650, 'SMITH');
insert into garage values ('BMW', 'SILV', 808, 'JOHNS');
insert into garage values ('BMW', 'GREE', 754, 'CARRO');
insert into garage values ('CHEV', 'WHIT', 532, 'JONES');
commit;

Let's query the time here:

select sysdate from dual;

SYSDATE
-------------------
2015-08-13 22:52:30

Do some modifications:

update garage set vin = 303 where vin = 808;
insert into garage values ('BUIC', 'BLAC', 400, 'JAMES');
commit;

Let's query the time again:

select sysdate from dual;

SYSDATE
-------------------
2015-08-13 22:52:42

Now let's use Flashback Version Query and find the modifications in that time period:

select
  garage.*,
  garage.rowid,
  versions_starttime as starttime,
  versions_operation as operation
from garage versions between timestamp 
timestamp'2015-08-13 22:52:30' and timestamp'2015-08-13 22:52:42'
where versions_operation is not null;

MAKE       COLOR       VIN DRIVER     ROWID              STARTTIME             OPERATION
---------- ---------- ---- ---------- ------------------ --------------------- ----------
BUIC       BLAC        400 JAMES      AAAWKFAAEAAAAJcAAE 13-AUG-15 10.52.36 PM I
BMW        SILV        303 JOHNS      AAAWKFAAEAAAAJcAAB 13-AUG-15 10.52.36 PM U

Here you can see BMW, SILV row was updated (U in column OPERATION), and BUIC, BLAC row was inserted (I in column OPERATION).

(Note that a ROWID may change.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.