Lesson 6: Clearing out the pre-image and delete tables

In this lesson, you create an upload_end hook to clear out the pre-image and delete tables.

Prérequis

This lesson assumes you have the roles and privileges listed in the Privileges section at the start of this tutorial: Tutorial: Using scripted upload.

This lesson assumes you have completed all preceding lessons. See Lesson 1: Creating the consolidated database.

Contexte et remarques

This tutorial uses the default setting for the dbmlsync extended option LockTables, so the tables are locked during synchronization. So, you do not have to worry about leaving rows in the tables for operations that occurred after the end_progress. Locking prevents such operations from occurring.

Task

Using the instance of Interactive SQL connected to the remote database, create an upload_end hook to clean up the employee_preimage and employee_delete tables when an upload is successful.

CREATE PROCEDURE sp_hook_dbmlsync_upload_end()
BEGIN
    DECLARE val   varchar(256);
    
    SELECT value
    INTO val 
    FROM #hook_dict
    WHERE name = 'upload status';
    
    IF val = 'committed' THEN
      DELETE FROM employee_delete;
      DELETE FROM employee_preimages;
    END IF;
END;

Résultat

The pre-image and delete results are removed from the tables when an upload is successful.

Suivant

Proceed to Lesson 7: Creating a publication, MobiLink user, and subscription.

Discuter à propos de cette page dans DocCommentXchange.