Query to revert db changes to a specific time by checking audit data

user_3302016 0
Viewed 89

I have a module which handles some business rules in my application, there are a few tables where these rules are stored.

Orignal Business Rule table : br_tbl_1

br_id | col_1 | col_2 
------+-------+--------
 1    |  a    |   myk
 2    |  b    |   abc

Related Tables: br_tbl_2

id | br_id | col_1 
---+-------+--------
 1 |  1    |   something
 2 |  1    |   something_else
 3 |  2    |   Another thing

and so on...

Now to track the changes made to the business rules, I have an audit table for each of the above tables, like so..

Business Rule Audit Table: br_tbl_1_audit

id | br_id |  col_1 | col_2 | audit_dtme          | operation
---+-------+--------+-------+---------------------+-----------------
 1 |  1    |   a    | xyz   | 01-01-2001 12:30:10 |   INSERT 
 2 |  1    |   a    | myk   | 02-01-2001 01:00:00 |   UPDATE
 3 |  2    |   b    | abc   | 02-01-2001 01:10:30 |   INSERT

by looking at the data from br_tbl_1_audit table we can see that the value for col_2 for br_id = 1 has changed from "xyz" to "myk"

Similarly we have an audit table for the other business rules tables.

Related Table's Audit Table: br_tbl_2_audit

id | br_id | col_1            | audit_dtme           |  operation 
---+-------+------------------+----------------------+--------------
 1 |  1    |   something      | 01-01-2001 12:30:10  |  INSERT 
 2 |  1    |   something_else | 01-01-2001 12:30:10  |  INSERT
 3 |  2    |   Another thing  | 02-01-2001 01:10:30  |  INSERT

I need a Query which takes in a br_id and an audit_date_time and rolls back all the data for that br_id in all tables to that audit_dtme

I can do this with a Script, however I am not very good with SQL Queries, I appriciate the help.

FYI : I am using Postgres, but any SQL should be enough t push me in the right direction.

rdbms postgresql sql
2 Answers

In any given table, you can use distinct on:

select distinct on (a.br_id) a.*
from br_tbl_1_audit a
where a.audit_dtime <= $audit_date_time
order by a.br_id, a.audit_dtime desc;

You can also filter for one or more br_id values as well.

You can repeat this for all the tables you care about.

If you need to replace a row, then you can use update:

update br_tbl_1 t
    set col_1 = a.col_1,
        col_2 = a.col_2
    from (select a.*
          from br_tbl_1_audit a
          where a.audit_dtime <= $audit_date_time and
                a.br_id = 1
          order by a.audit_dtime desc
          limit 1
         ) a
    where t.br_id = 1;
user_11440350

I would probably say that this would be very tough to handle if you have too many tables linked.

Following is the sample code if you have to just delete from one table. Now you can modify this as per your requirement.

declare @id int, 
        @br_id int, 
        @br_id_input int = 1, 
        @col_1 varchar(100), 
        @col_2 varchar(100), 
        @audit_dtme datetime, 
        @operation varchar(100), 
        @audit_date_time datetime = '2001-01-01 12:30:10.000';

declare cur cursor 
for select id, br_id, col_1, col_2, audit_dtme, operation 
    from br_tbl_1_audit 
    where br_id = @br_id_input and audit_dtme > @audit_date_time 
    order by id desc

open cur

fetch next from cur into @id, @br_id, @col_1, @col_2, @audit_dtme, @operation

while @@fetch_status = 0
begin

    if (@operation = 'INSERT')
    begin
        delete from br_tbl_1 where br_id = @br_id;
    end
    else if (@operation = 'DELETE')
    begin
        set identity_insert br_tbl_1 on;
        insert into br_tbl_1 (br_id, col_1, col_2)
        values (@br_id, @col_1, @col_2)
        set identity_insert br_tbl_1 off;
    end
    else
    begin
        ;with cte
        as 
        (
            select top 1 * from br_tbl_1_audit 
            where br_id = @br_id and audit_dtme < @audit_dtme
            order by id desc
        )
        update tb1
        set tb1.col_1 = cte.col_1,
            tb1.col_2 = cte.col_2
        from br_tbl_1 tb1
             join cte on cte.br_id = tb1.br_id
    end

    delete from br_tbl_1_audit where id = @id;

    fetch next from cur into @id, @br_id, @col_1, @col_2, @audit_dtme, @operation
end

close cur
deallocate cur

For deleting in the foreign key tables, you will have to add another cursor inside the main cursor which will insert/update/delete in the foreign key tables as per the primary key table rows.

Although cursor may not be the best solution as it may be slow if there are too many tables or data rows to restore.

user_44779690
Related