0 votes

I performed a data realignment datasynchro of documents to update one field.
Consequently, the date of last modification (D_CHANGE) as well as the code of the user who performed the last modification (CHANGEDBY) are (almost) lost.

I wrote a statement to recover these 2 values.
Can anybody (comfortable with sql statements) validate my statement?
Is there a simpler way to achieve this recovery?

update documents d set 
D_CHANGE = (
  SELECT d_change from ( -- Before Last date of update or insertion of the record
    select ROWNUM AS RNUM, sc.*  
    from
    (
      select sc.d_change
      from SYS_CHANGED sc 
      where k_table = 31000 and k_1 = d.K_DOCUMENT
      and operation in ('B', 'A') -- UPDATE OR INSERT
      order by d_change, milli desc
    ) sc
  )
  where RNUM = 2 -- Before Last INSERT or UPDATE operation
),
CHANGEDBY = (
  SELECT username as username from ( -- Before Last user that updated or inserted the record
    select ROWNUM AS RNUM, sc.* 
    from
    (
      select sc.username
      from SYS_CHANGED sc 
      where k_table = 31000 and k_1 = d.K_DOCUMENT
      and operation in ('B', 'A') -- UPDATE OR INSERT
      order by d_change, milli desc
    ) sc
  )
  where RNUM = 2 -- Before Last INSERT or UPDATE operation
)
where k_document in ( -- Select document keys linked to my publication, for which the last modification has been done by the admin user
    select d.K_DOCUMENT 
    from docu_publ dp
    inner join documents d on d.K_document = dp.K_document
    where dp.k_publication = 4394 and d.changedby = 'ADMINEBIS'
);
asked in Database by (243 points)

1 Answer

+1 vote

Hello Pierre.

If I understand well, you want to exclude the last change done by user 'ADMINEBIS'. So, I suggest to exclude SYS_CHANGED records from this usercode.

Also note that you didn't sort descending on D_CHANGE, only on milli.

If the DB is Microsoft SQL Server:

  • You don't need to use the column MILLI. It was added for Oracle
  • The top statement does respect the order clause, in constrast with rownum on Oracle.

So, this could be one of your sub-queries in the update.

select top 1 D_CHANGE from SYS_CHANGED
where K_TABLE = 31000 and K_1=d.K_DOCUMENT
and OPERATION in ('B', 'A') and USERNAME<>'ADMIN'
order by D_CHANGE desc, MILLI desc

If you are afraid of doing the wrong update, here are some tips, assuming there is already a decent backup:

  • Make a duplicate of the DOCUMENTS table before the update
  • Do the update within a transaction

SQL Server syntax:

-- Begin transaction
begin tran

-- Do your update statement

-- Check your update
Select ...

-- Based on the result, rollback or commit
rollback tran
commit tran
answered by (7.4k points)
The statement I wrote is for Oracle. The trick to get the nth ordered record is unfortunately more complex.

When I added milli to the d_change order clause, I forgot to duplicate the desc parameter...
Thanks for the correction and the recover procedure advices.
For Oracle, the transaction mechanism is in-built, meaning that any edit statement won't be committed without an explicit call to the... commit statement.

Functionally speaking, excluding all ADMIN sys_changed records will erase previous ADMIN operations... and that's also a good idea; keeping "change fields" dedicated to human operations.
1,248 questions
1,518 answers
1,858 comments
328 users