Configuration Guide

10.4 HotFix 2

Back Next

Direct Delete with End Date Code Example

Your organization needs to detect hard deletes for a staging table called C_CUSTOMER_STG. They want to perform a direct delete of the hard-deleted records in the base object and assign an end date to the deleted records.

You need to insert the metadata that you need to detect hard deletes into the hard delete detection table for the C_CUSTOMER_STG staging table. When you detect hard deletes, the

MDM Hub

must provide a delete flag in addition to an end date for the deleted record. The name of the delete flag column in the example is DEL_CODE and the name of the end date column in the staging table in the example is END_DATE.

The following sample code inserts metadata into the hard delete detection table to perform a direct delete with an end date for the deleted records:

On Oracle.

INSERT into c_repos_ext_hard_del_detect
	(rowid_table, hdd_enabled, hdd_type, delete_flag_column_name, delete_flag_val_active, delete_flag_val_inactive,  has_end_date, end_date_column_name, end_date_val)
SELECT rowid_table, 
	1 AS hdd_enabled, 
	DIRECT AS hdd_type,
	'DEL_CODE' AS delete_flag_column_name, 
	'A' AS delete_flag_val_active, 
	'I' AS delete_flag_val_inactive, 
	1 AS has_end_date, 
	'END_DATE' AS end_date_column_name,
	'SYSDATE' AS end_date_val
FROM  c_repos_table
WHERE table_name = 'C_CUSTOMER_STG';

On Microsoft SQL Server.

INSERT INTO [dbo].[C_REPOS_EXT_HARD_DEL_DETECT]
           ([ROWID_TABLE], [HDD_ENABLED], [HDD_TYPE], [DELETE_FLAG_COLUMN_NAME], [DELETE_FLAG_VAL_ACTIVE], [DELETE_FLAG_VAL_INACTIVE], [HAS_END_DATE], [END_DATE_COLUMN_NAME], [END_DATE_VAL],)
SELECT [ROWID_TABLE]
           ,1
           ,'DIRECT'
           ,'DEL_CODE'
           ,'A'
           ,'I'
           ,1
           ,'END_DATE'
           ,GETDATE()
FROM [dbo].[C_REPOS_TABLE] 
WHERE table_name = 'C_CUSTOMER_STG';
GO

Direct Delete

Download Guide

Watch

Comments

Communities

Knowledge Base

Success Portal