Need data undo if DML changes done USING WITH Cursor

Transact-SQL
1

Hi Every one,

i have created Trigger & Audit Table for Destination Table .

My query is if i deleted or updated / inserted on destination table and trigger fired and its stored in Audit table .
how can i undo the DML changes latest records using with cursor ,

  1. how can i pull data from Audit table and restored in Destination Table .
  2. i have to check only last transaction in Audit table and restored in destination table ,passing POLICYNO as parameter.

DESTINATION TABLE.

RowNo CLAIMTYPE POLICYNO
20090 I NP000710
20089 I N000710
20090 I BP000710
20089 I BP000710
20090 I DP000710

AUDIT TABLE

Rowid ModDate ModBy Action RowNo CLAIMTYPE POLICYNO
1 2018-03-06 04:48:54.043 SYS\Raj UO 20090 I BP000710
2 2018-03-06 04:48:54.043 SYS\Raj UO 20089 I DP000710
3 2018-03-06 04:48:54.043 SYS\Raj UN 20090 I SP000710
4 2018-03-06 04:48:54.043 SYS\Raj UN 20089 I GP000710
5 2018-03-06 04:48:54.047 SYS\Raj D 20090 I DP000710

Can any one help me on this.

Thanks

rajnidas

2

Something like...

update dt
set dt.policyno = aud.policyno
from destinationtable dt
join (select rn = row_number() over (partition by rowno order by moddate desc), rowno, policyno
       from audittable) at
  on dt.rowno = at.rowno
where at.rn = 1
3

Thank you so much gbritton,

its working .

Thanks ,

Rajnidas