Add a test in trigger deletion and check if the pipeline still exists

As a follow-up to Incident Review: INC-1482 (gitlab-com/gl-infra/production#19936 - closed), we need to account for a use case where a trigger deletion ends up deleting a pipeline.

---

We checked the pipelines in DB and they did not exist.

what happened? and why?

Deleting trigger deleted the pipelines. Some users have a workflow where they deleted the trigger after the pipeline is complete.

what could be done better next time? (e.g. any improved monitoring/alerts/detection mechanisms)

I think we can add a test in trigger deletion and check if the pipeline still exists. We did not anticipate this to be a workflow, so maybe we missed adding the test.

any follow-up corrective actions we should consider

We have the fix merged in master as of now from Change to async_nullify for p_ci_pipelines.trig... (!192968 - merged).

---

Yes, it's caused by the LFK added for p_ci_pipelines.trigger_id.

I started with the most restricted one async_delete when adding the LFK, because we have quite a few orphan records.

But I didn't realize that customers delete the trigger after they trigger a pipeline.

I think we can add a test in trigger deletion and check if the pipeline still exists. We did not anticipate this to be a workflow, so maybe we missed adding the test.

Yeah, agree. This should make sure that the pipeline won't be deleted when trigger is deleted.