TRIGGER

Starts (or queue for execution) one or more pipelines after this one completes. This operation allows you to daisy-chain pipelines and multicast change logs to additional targets. The type of trigger dictates the condition for starting it, such as on completion or on error.

At this time, you cannot pass startup parameters to a Trigger; if you need to provide input parameters to the pipeline started by the trigger, consider using staging tables, temporary files, message consumers, or using the START operation for simpler scenarios. If the trigger starts a Write pipeline, you can use the LOAD operation to read from the change log created by this pipeline; this is the preferred option to ensure proper playback sequencing and offers replay capabilities.

The Trigger operation can only be specified as the last command in a SQL CDC batch command.

Some DataZen agents limit the number of triggers available based on the SKU.

Looping

You can loop on the same pipeline by declaring a trigger on the same pipeline. This can be very useful when dealing with paged data sets, and when running the pipeline in a loop is desired until no more records are available.

Make sure to validate that the paging approach is working as expected to avoid entering into an infinite loop. Stopping an inifinite loop requires disabling the pipeline as this will prevent the next trigger from firing.

SELECT * FROM DB [sqlconnection]
(
 -- return 50 tables at a time ordered by date 
 SELECT TOP 50 * FROM sys.tables 
 WHERE create_date > ISNULL('@highwatermarknull', '2001-01-01')
 ORDER BY create_date)
WITH HWM 'create_date'; 

CAPTURE;

-- loop until no more tables are found
TRIGGER 'Self Trigger SQL' ON SUCCESS_WITH_DATA;
 

Starts another pipeline upon completion of the current one. This operation can only be used as the end of a SQL CDC script.

TRIGGER '...' 
    ON < SUCCESS | SUCCESS_WITH_DATA | COMPLETION | ERROR > 
    { DELAY N }
;

ON	The condition to trigger the next pipeline: SUCCESS, SUCCESS_WITH_DATA, COMPLETION, ERROR
DELAY	The number of seconds to wait at a minimum before starting the trigger (0 = no delay)

Example 1

-- Get data from an RSS Feed
SELECT * FROM HTTP [Rss Feed] (GET /econi.xml) APPLY TX (//item);

-- Start one or more pipelines depending on the outcome of this pipeline
TRIGGER 'pipeline_notify' ON SUCCESS;
TRIGGER 'pipeline_sendemail' ON FAILURE;
TRIGGER 'pushToDrive' ON COMPLETION DELAY 30;