CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Error_2646
Error_2646

Reputation: 3791

Determine if row has ever been updated

Might be a long shot, but I'm looking for a way to see if a row has ever been updated since it was inserted.

Ex.

CREATE TABLE TEST_DATA AS
  ( SELECT 'A' AS dummy
      FROM dual
     UNION
    SELECT 'B' AS dummy
      FROM dual
  );

UPDATE TEST_DATA 
   SET dummy = 'C'
 WHERE dummy = 'B';

Is there any way I can tell that the 'C' record has been updated?

Also, I have absolutely no control over the data model so I can't add an add timestamp and an update timestamp to the actual table.

Upvotes: 3

Views: 128

Answers (1)

Nikhil
Nikhil

Reputation: 3950

this will work:

create table test_data(a varchar(1));
insert into test_data values('A');
insert into test_data values('B');
insert into test_data values('C');
insert into test_data values('D');
select * from test_data;

 A
 B
 C
 D
create table noofchanges(data varchar(1),numberofchanges int) ;
insert into noofchanges(data,numberofchanges) select a,0 from test_data;
select * from noofchanges;

A   0
B   0
C   0
D   0


CREATE OR REPLACE TRIGGER test_data_before_update
BEFORE UPDATE
   ON test_data
   FOR EACH ROW
BEGIN
    update noofchanges 
    set numberofchanges=numberofchanges+1 
    where data=:old.a;    
END;


update test_data set a='A' where a='B';
select * from test_data;


 A 
 A
 C
 D

select * from noofchanges
A   0
B   1
C   0
D   0

thank you!!!!!!

Upvotes: 3

Related Questions