::NO_DATA_TO_PROCESS::
A schedule trigger in Oracle BI Publisher lets a report decide at runtime whether it should run, based entirely on whether a driving SQL query returns any rows. Using a simple EXISTS-based query, you can turn the trigger into a clear “yes/no” gate for your scheduled jobs.
What a schedule trigger does
A schedule trigger is tied to a scheduled job and runs a SQL query at the moment the job is due to execute. If the query returns at least one row, the job runs; if it returns no rows, the job instance is skipped.
Because the trigger is just SQL, it can encapsulate almost any business rule that can be expressed in the database: inventory thresholds, period status checks, or whether new data has arrived since the last run. This keeps scheduling logic close to the data, rather than buried in external scripts or manual procedures.
Where schedule triggers live
The schedule trigger is created in a BI Publisher data model and is of type Schedule with language SQL Query. Importantly, the trigger does not need to live in the same data model as the report it controls, so a single trigger definition can be reused across multiple reports that share the same business rule.
When defining the trigger, you choose the data source, enter the SQL text, and optionally enable caching of the trigger results. Caching is useful when many jobs can share the same evaluation outcome over a period, for example “is today a working day for this calendar?”.
The example trigger query
SELECT 'TRUE' AS DATA_RETURNED
FROM DUAL
WHERE EXISTS (
SELECT 1
FROM DUAL
WHERE 1 = 1 -- put your real condition here
);
This pattern pushes the business rule into the EXISTS predicate, which is where you will eventually express the real condition that decides whether the report should run. The outer SELECT simply returns a single column, DATA_RETURNED, when the condition evaluates to true.
In the current form, the inner query uses DUAL with a static condition 1 = 1, which is always true in Oracle, so EXISTS always evaluates to true and returns one row. Because BI Publisher’s schedule trigger engine only checks whether any row is returned, the presence of that row is enough to allow the job to proceed; the literal 'TRUE' value is mainly for readability and optional logging.
Evolving the query into a real control
To turn this into a meaningful control for your scheduled job, you replace the placeholder condition in the inner query with logic that reflects your actual business requirement. For example, you might:
Check whether any new transactional data has been loaded since the last successful run.
Verify that the accounting period for a given ledger is open.
Deliver reports/alerts when there is data to be reported.
The key point is that the inner SELECT must return at least one row when the report is allowed to run, and no rows when it should be skipped. The outer SELECT does not need to change; it remains a simple, declarative wrapper around the existence check.
Using the query in BI Publisher
When configuring the schedule trigger in your data model, you:
Create a new event trigger of type Schedule and leave the language as SQL Query.
Paste this query into the SQL Query area and point it at the appropriate data source.
Optionally introduce parameters (for ledger, business unit, date, etc.) and reference them in the inner query, so the same trigger definition can drive multiple scheduled jobs with different runtime values.
With this structure, the schedule trigger acts as a small, focused gatekeeper: the EXISTS logic determines eligibility, and BI Publisher translates “row or no row” into “run or skip” for each scheduled instance.
Need some assistance?
Ready to bring clarity to customer, supplier and employee communications? With BI Publisher, we specialise in customising customer, supplier, and employee-facing documents such as invoices, purchase orders, payslips, and statements to meet your exact branding, compliance, and operational needs. From layout design to data logic enhancements, we help you deliver clear, consistent, and impactful outputs across your organization. Contact us today to discover how our expertise in BI Publisher can streamline your document processes and elevate your business communications.
