Triggers & Scheduling¶

CREATE JOB job_name
SCHEDULE 'cron_expression'
[TIMEZONE 'timezone']
[ENABLED true|false]
[DESCRIPTION 'description']
AS
BEGIN
    -- procedure body
END JOB

CREATE JOB daily_cleanup
SCHEDULE '0 2 * * *'
TIMEZONE 'UTC'
DESCRIPTION 'Archive logs older than 30 days'
AS
BEGIN
    DECLARE old_logs ARRAY
    SET old_logs = ESQL_QUERY('FROM logs-* | WHERE @timestamp < NOW() - 30 DAYS | LIMIT 10000')
    PRINT 'Archiving ' || ARRAY_LENGTH(old_logs) || ' old logs'

    FOR doc IN old_logs LOOP
        CALL archive_to_s3(doc)
    END LOOP
END JOB

CREATE JOB hourly_health_check
SCHEDULE '@hourly'
AS
BEGIN
    DECLARE error_count NUMBER
    SET error_count = ESQL_QUERY(
        'FROM logs-* | WHERE level = ''ERROR'' AND @timestamp > NOW() - 1 HOUR | STATS count = COUNT(*)'
    )[0].count

    IF error_count > 100 THEN
        CALL SLACK_SEND('#alerts', '⚠️ High error rate: ' || error_count || ' errors in last hour')
    END IF
END JOB

-- Disable a job
ALTER JOB daily_cleanup DISABLE

-- Enable a job
ALTER JOB daily_cleanup ENABLE

-- Change schedule
ALTER JOB daily_cleanup SCHEDULE '0 3 * * *'

-- Delete a job
DROP JOB daily_cleanup

-- List all jobs
SHOW JOBS

-- Show job details
SHOW JOB daily_cleanup

-- Show execution history
SHOW JOB RUNS FOR daily_cleanup

CREATE TRIGGER trigger_name
ON INDEX 'index_pattern'
[WHEN condition]
[EVERY interval]
[ENABLED true|false]
[DESCRIPTION 'description']
AS
BEGIN
    -- @documents: array of matched documents
    -- @document_count: number of matched documents
END TRIGGER

CREATE TRIGGER on_payment_error
ON INDEX 'logs-*'
WHEN level = 'ERROR' AND service = 'payment'
EVERY 5 SECONDS
DESCRIPTION 'Alert team on payment service errors'
AS
BEGIN
    FOR doc IN @documents LOOP
        CALL SLACK_SEND('#payment-alerts', 
            '🚨 Payment Error: ' || doc.message || ' | User: ' || doc.user_id
        )

        IF doc.error_code = 'PAYMENT_DECLINED' THEN
            CALL PAGERDUTY_TRIGGER('Payment system issue', 'high', doc)
        END IF
    END LOOP
END TRIGGER

CREATE TRIGGER on_security_event
ON INDEX 'security-events-*'
WHEN event_type IN ('login_failed', 'unauthorized_access')
EVERY 10 SECONDS
AS
BEGIN
    IF @document_count > 10 THEN
        CALL SLACK_SEND('#security', 
            '🔒 Security Alert: ' || @document_count || ' suspicious events'
        )
    END IF

    FOR doc IN @documents LOOP
        CALL log_security_event(doc)
    END LOOP
END TRIGGER

CREATE TRIGGER on_error_spike
ON INDEX 'logs-*'
WHEN level = 'ERROR'
EVERY 1 MINUTE
AS
BEGIN
    IF @document_count > 50 THEN
        CALL SLACK_SEND('#alerts', 
            '📈 Error spike: ' || @document_count || ' errors in last minute'
        )
    END IF
END TRIGGER

-- Disable a trigger
ALTER TRIGGER on_payment_error DISABLE

-- Enable a trigger
ALTER TRIGGER on_payment_error ENABLE

-- Change poll interval
ALTER TRIGGER on_payment_error EVERY 10 SECONDS

-- Delete a trigger
DROP TRIGGER on_payment_error

-- List all triggers
SHOW TRIGGERS

-- Show trigger details
SHOW TRIGGER on_payment_error

-- Show execution history
SHOW TRIGGER RUNS FOR on_payment_error

-- Critical alerts: short interval
CREATE TRIGGER critical_alerts ON INDEX 'logs-*'
WHEN level = 'CRITICAL'
EVERY 1 SECOND  -- Fast response needed
...

-- Regular monitoring: longer interval
CREATE TRIGGER daily_summary ON INDEX 'metrics-*'
EVERY 5 MINUTES  -- Aggregated view is fine
...

-- Good: Process all documents together
CREATE TRIGGER batch_processor
ON INDEX 'events-*'
EVERY 30 SECONDS
AS
BEGIN
    -- Process all at once
    CALL bulk_process(@documents)
END TRIGGER

-- Avoid: Individual processing with external calls
CREATE TRIGGER slow_processor
ON INDEX 'events-*'
EVERY 1 SECOND
AS
BEGIN
    -- This could be slow with many documents
    FOR doc IN @documents LOOP
        CALL HTTP_POST('https://api.example.com', doc)  -- Slow!
    END LOOP
END TRIGGER

CREATE TRIGGER robust_handler
ON INDEX 'logs-*'
WHEN level = 'ERROR'
EVERY 10 SECONDS
AS
BEGIN
    FOR doc IN @documents LOOP
        TRY
            CALL SLACK_SEND('#alerts', doc.message)
        CATCH
            PRINT 'Failed to send alert: ' || @error.message
            -- Don't fail entire trigger
        END TRY
    END LOOP
END TRIGGER