DELETE RELATED RECORDS FROM MULTIPLE tables using trigger
Hey all,
I have a main table called TRAFFIC. In this table i have one column called citationnumber(what citation a person is issued when they are stopped), and another column named GUID - the unique identifier for each row. There are many tables that relate to table TRAFFIC via the GUID column. What i need done is, find all citationnumber's that end in '0010', then delete all related records in the other tables. Here is what i have so far: I am not sure if this is the best way to approach this, but any help would be great.
I need to make this into a script, so it can be run nightly...
create or replace
TRIGGER control_num
BEFORE DELETE ON traffic
FOR EACH ROW
when (substr(old.citationnumber,-4,4)='0010')
BEGIN
DELETE FROM WITNESS WHERE tbl_traffic_WITNESS.guid = :old.guid;
DELETE FROM CITATION_INFO WHERE TBL_TRAFFIC_CITATION_INFO.GUID = :old.guid;
DELETE FROM PERSON WHERE TBL_TRAFFIC_PERSON.GUID = :old.guid;
DELETE FROM VEHICLE WHERE TBL_TRAFFIC_VEHICLE.GUID = :old.guid;
END control_num;
/
delete from master.tbl_traffic where SUBSTR(citationnumber, -4,4)='0010';