How To Delete an Occurrence

Owned by matthew.staynor
Last updated: Aug 18, 2022 by Haoyuan Wang (Unlicensed)
2 min read

  1. Find the occurrence’s id

  2. The three tables that an occurrence automatically creates references to the occurrence in are AY033_INVESTIGATION_ACTION, AY027_INVESTIGATION, and AY019_TC_OCCURRENCE. Delete any rows with the matching occurrence ID from these three tables in the order they are listed above. If the occurrence is further along than brand new, you may receive a pop up error stating that a row cannot be removed from AY019 or AY027 because there exists reference in a different table; simply go to the listed table and remove any row that is listed to the occurrence id.

After this, the occurrence should be deleted.

Below is a sample sql to delete occurrence with TSB number ‘A22F1234’.

first find the TC_OCCURRENCE_ID

select * from AY019_TC_OCCURRENCE where tsb_occurrence_id ='A22F1234'

In some tables TC_OCCURRENCE_ID is referenced as INVESTIGATION_ID.

then replace TC_OCCURRENCE_ID or INVESTIGATION_ID, and run the following sql:

delete from AY033_INVESTIGATION_ACTION where investigation_id = 72526;

delete from ay013_inspector where investigation_id = 72526;

delete from AY053_REPORT_AIR_DETAIL where investigation_report_id = 993;

delete from AY050_INVESTIGATION_REPORT where investigation_id = 72526;

delete from AY028_INVESTIGATION_REGION where investigation_id = 72526;

delete from AY027_INVESTIGATION where investigation_id = 72526;

delete from AY012_INJURY where TC_OCCURRENCE_ID = 72526;

delete from AY019_TC_OCCURRENCE where tsb_occurrence_id ='A22F1234';