before and after trigger in oracle can we implement in gcp bigquery

[NishinThattil]

Triggers can run BEFORE the action is taken or AFTER the action is taken.

Can we implement is bigquery.

[ms4446 · Accepted solution]

In BigQuery, there is no native support for triggers like those available in Oracle (e.g., before and after triggers). However, you can achieve similar functionality using other methods:

1. Cloud Functions or Cloud Run: You can set up Google Cloud Functions or Cloud Run to act as triggers that respond to certain events in BigQuery, such as the completion of an insert or update. This can be done by:

   • Writing a Cloud Function or Cloud Run service that is triggered by Cloud Pub/Sub messages.
   • Using the BigQuery audit logs to detect data changes and trigger your function.

2. Scheduled Queries: For cases where you don't need real-time triggers but want periodic checks on data, you can use scheduled queries to process or modify data at regular intervals.

3. Dataflow for Real-time Streaming: If you are working with streaming data, Google Cloud Dataflow can process real-time changes, and you can add logic to process rows as they are ingested into BigQuery.

4. Manual Logic in Queries: You can manually handle logic such as "before insert" or "after insert" by structuring your queries to first validate data before inserting, or running updates after an insert.

[ms4446 · Accepted solution]

In BigQuery, there is no native support for triggers like those available in Oracle (e.g., before and after triggers). However, you can achieve similar functionality using other methods:

1. Cloud Functions or Cloud Run: You can set up Google Cloud Functions or Cloud Run to act as triggers that respond to certain events in BigQuery, such as the completion of an insert or update. This can be done by:

   • Writing a Cloud Function or Cloud Run service that is triggered by Cloud Pub/Sub messages.
   • Using the BigQuery audit logs to detect data changes and trigger your function.

2. Scheduled Queries: For cases where you don't need real-time triggers but want periodic checks on data, you can use scheduled queries to process or modify data at regular intervals.

3. Dataflow for Real-time Streaming: If you are working with streaming data, Google Cloud Dataflow can process real-time changes, and you can add logic to process rows as they are ingested into BigQuery.

4. Manual Logic in Queries: You can manually handle logic such as "before insert" or "after insert" by structuring your queries to first validate data before inserting, or running updates after an insert.

[mpinlac · Accepted solution]

Hi @NishinThattil,

In addition to @ms4446 , you can also refer to the following documentation: 

• How to trigger Cloud Run actions on BigQuery events
• Create a trigger for Cloud Run functions (2nd gen)

I hope the above information is helpful.