Update record and then insert the same record with revision amount-CodePudding

How to update ETLDeletedatetime in the 4th row Target table and insert the revision amount was got updated in ETLDeletedatetime?

OrderID identity (1,1) on both tables

Source table orderID 1 have been delete and I need the Target table orderID 1 to update with ETLDeletedatetime. Then I need insert new record of negative 300.00 amount in Target table

Source Table

OrderID	Amount	CreatedDatetime	UpdatedDatetime	Deletedatetime
4	100.00	2022-07-15	2022-07-15	NULL
5	1100.00	2022-07-15	2022-07-18	NULL
6	200.00	2022-07-15	2022-07-15	NULL
NULL	NULL	NULL	NULL	NULL
NULL	NULL	NULL	NULL	NULL

Target Table

OrderID	Amount	CreatedDatetime	UpdatedDatetime	Deletedatetime	ETLInsertdatetime	ETLUpdatedDatetime	ETLDeletedatetime
4	100.00	2022-07-15	2022-07-15	NULL	2022-07-15	2022-07-15	NULL
5	1100.00	2022-07-15	2022-07-18	NULL	2022-07-15	2022-07-18	NULL
6	200.00	2022-07-15	2022-07-15	NULL	2022-07-15	2022-07-15	NULL
1	300.00	2022-07-15	2022-07-15	NULL	2022-07-15	2022-07-15	NULL
3	400.00	2022-07-15	2022-07-15	NULL	2022-07-15	2022-07-15	2022-07-18

End goal

OrderID	Amount	CreatedDatetime	UpdatedDatetime	Deletedatetime	ETLInsertdatetime	ETLUpdatedDatetime	ETLDeletedatetime
4	100.00	2022-07-15	2022-07-15	NULL	2022-07-15	2022-07-15	NULL
5	1100.00	2022-07-15	2022-07-18	NULL	2022-07-15	2022-07-18	NULL
6	200.00	2022-07-15	2022-07-15	NULL	2022-07-15	2022-07-15	NULL
1	300.00	2022-07-15	2022-07-15	NULL	2022-07-15	2022-07-15	2022-07-19
3	400.00	2022-07-15	2022-07-15	NULL	2022-07-15	2022-07-15	2022-07-18
7	-300.00	2022-07-19	2022-07-19	NULL	2022-07-19	2022-07-19	NULL

--Source table

create Table Source (
OrderID Int Identity (1,1) Primary key,   
Amount Decimal(30,2) Not Null,  
CreatedDatetime Datetime Default GetDate() Not Null,   
UpdatedDatetime datetime Default GetDate(),  
DeleteDatetime datetime)

--Target table

create Table TargetCDC (  
OrderID Int not null,   
Amount Decimal(30,2) Not Null,  
CreatedDatetime Datetime ,   
UpdatedDatetime datetime ,  
DeleteDatetime datetime,  
ETLInsertDateime Datetime Default Getdate () not Null,  
ETLUpdatedDatetime datetime default GetDate (),  
ETLDeteleteDatetime datetime)

--Insert source table

Insert INTO Source (Amount)  
values (300.00), (310.00), (400.00),(100.00),(1100.00),(200.00)

--Insert target table

Insert INTO TargetCDC (Amount)  
values (300.00), (310.00), (400.00),(100.00),(1100.00),(200.00)

--Delete amount 400.00 from source table

Delete from Source  
where OrderID = '3'

--Update Target table with ETLDeteleteDatetime

Update CDC  
set CDC.ETLDeteleteDatetime  = getdate()  
FROM Source   
full outer JOIN TargetCDC CDC ON Source.Orderid = CDC.OrderID  
where source.orderid is null

--Delete orderid 1 amount 300.00 from source table

Delete from Source  
where OrderID = '3'

--In one query, I need to update orderid 1 amount 300.00 with ETLDeteleteDatetime in target table and insert new record with revisal amount. This is something that I don't know.

CodePudding user response：

Use a after delete trigger. And insert into the TargetCDC table from the deleted virtual table

create trigger trSourceDelete
on Source
for after delete
as
begin
     insert into TargetCDC (OrderID, Amount, ETLDeteleteDatetime)
     select OrderID, Amount, ETLDeteleteDatetime = getdate()
     from   deleted
end

CodePudding user response：

Have you tried something like this?

INSERT INTO [TargetCDC] ([OrderID],[Amount])
SELECT -[x].[OrderID], -[x].[Amount]
FROM (
    UPDATE [CDC]
    SET [CDC].[ETLDeteleteDatetime] = GETDATE()
    OUTPUT inserted.[OrderID], inserted.[Amount]
    FROM [TargetCDC] [CDC]
    LEFT JOIN [Source] on [Source].[OrderID] = [CDC].[OrderID]
    WHERE [Source].[OrderID] IS NULL
) AS [x];