In the right pane, select CarDealerNotifier and, from the File menu, choose Properties.

Click the Events tab.

Choose begin_poll from the Events list.

Type the following SQL script in the provided text field:

--
-- Insert the last consolidated database 
-- modification date into @last_modified 
--
DECLARE @last_modified timestamp;
SELECT MAX(last_modified) INTO @last_modified FROM Dealer;

--
-- Delete processed requests if the mluser is up-to-date
--
DELETE FROM PushRequest
    FROM PushRequest AS p, ml_user AS u, ml_subscription AS s
    WHERE p.status = 'processed'
        AND u.name = p.mluser
        AND u.user_id = s.user_id
        AND @last_modified <= GREATER(s.last_upload_time, s.last_download_time);

--
-- Insert new requests when a device is not up-to-date
--
INSERT INTO PushRequest(mluser, subject, content) 
SELECT u.name, 'sync', 'ignored'
    FROM ml_user as u, ml_subscription as s
    WHERE u.name IN (SELECT name FROM ml_listening WHERE listening = 'y')
        AND u.user_id = s.user_id
        AND @last_modified > greater(s.last_upload_time, s.last_download_time)
        AND u.name NOT LIKE '%-dblsn'
        AND NOT EXISTS(SELECT * FROM PushRequest 
            WHERE PushRequest.mluser = u.name
                AND PushRequest.subject = 'sync')

In the first major section of the begin_poll script, processed requests from the PushRequest table are eliminated if a device is up to date:

@last_modified <= GREATER(s.last_upload_time, s.last_download_time)

@last_modified is the maximum modification date in the consolidated database Dealer table. The expression greater( s.last_upload_time, s.last_download_time) represents the last synchronization time for a remote database.

You can also delete push requests directly using the request_delete event. However, the begin_poll event, in this case, ensures that expired or implicitly dropped requests are not eliminated before a remote database synchronizes.

The next section of code checks for changes in the last_modified column of the Dealer table and issues push requests for all active MobiLink Listeners (listed in the ml_listening table) that are not up to date:

@last_modified > GREATER(s.last_upload_time, s.last_download_time)

When populating the PushRequest table, the begin_poll script sets the subject to 'sync'.

Choose request_cursor from the Events list.

Type the following SQL script in the provided text field:

SELECT
    p.req_id,
    'Default-DeviceTracker',
    p.subject,
    p.content,
    p.mluser,
    p.resend_interval,
    p.time_to_live
    FROM PushRequest AS p

The PushRequest table supplies rows to the request_cursor script.

The order and values in the request_cursor result set is significant. The second parameter, for example, defines the default gateway Default-DeviceTracker. A device tracking gateway keeps track of how to reach users and automatically selects UDP or SMTP to connect to remote devices.

Choose request_delete from the Events list.

Type the following SQL script in the provided text field:

UPDATE PushRequest SET status='processed' WHERE req_id = ?

Instead of deleting the row, this request_delete script updates the status of a row in the PushRequest table to 'processed'.