13.4 How do I…Perform cascading deletes with database triggers?

Problem

When my application deletes data in parent tables referenced by child tables, I want to ensure that data is deleted from the child tables to maintain proper referential integrity in the database. In order to ensure that the child records are deleted, I want to use database triggers. I know that when I use referential integrity constraints, I can make cascade deletes automatically. However, in some cases I do not want to use constraints, but still want the cascade delete operation. How do I perform cascading deletes using database triggers?

Technique

The BEFORE DELETE trigger can be used to delete child records referencing the record being deleted in the primary table. By placing DELETE statements in a BEFORE DELETE trigger, you can ensure that the child records are deleted. This technique cannot be used when referential integrity constraints exist, but can be used to perform more complex logic relating to the cascading deletes. The parent table is referred to as the constraining table when tables are related with referential integrity constraints. In this case, the trigger is not allowed to change values of the PRIMARY KEY, FOREIGN KEY, and UNIQUE key columns of the constraining table.

Steps

1. Run SQL*Plus and connect as the WAITE user account. CHP13_16.SQL, shown in Figure 13.16, creates the sample tables and data used in this How-To.

The script creates two sample tables for demonstrating cascading deletes. The CUST13 table contains detailed information about customers. The ORDER13 table contains the orders placed by the customers. When a customer is deleted from the CUST13 table, all associated records in the ORDER13 table should also be deleted. Sample records are created in both tables to demonstrate the process. Run the script to create the sample tables and data.

SQL> START CHP13_16

Table created.

Table created.

1 row created.

1 row created.

1 row created.

Commit complete.

2. Run the CHP13_17.SQL file in SQL*Plus. The CREATE TRIGGER statement contained in the file creates a BEFORE UPDATE trigger on the CUST13 table to delete child records referencing a deleted record. Figure 13.17 displays the results of the operation.

Line 1 provides the required keywords to create or replace a trigger and specifies the trigger name. Line 2 specifies the triggering event which causes the trigger to fire, i.e. before a row is deleted from the CUST13 table. The FOR EACH ROW clause in line 3 ensures that the trigger is fired once for each row being deleted. Lines 4 through 7 contain the trigger body. The DELETE statement in lines 5 and 6 removes child records from the ORDER13 table. The :OLD.CUST_NO column is used in the WHERE clause of the DELETE statement to delete orders that belong to the customer deleted from the CUST13 table.

When records are deleted from the CUST13 table, the trigger can delete zero or more records from the ORDER13 table. In the next step, a record is deleted from the CUST13 table to demonstrate the trigger operation.

3. Run the CHP13_18.SQL file in SQL*Plus, as shown in Figure 13.18. The DELETE statement contained in the file deletes a row from the CUST13 table, causing the trigger to fire and the cascading delete to occur.

The WHERE clause in line 3 specifies that all customers with a value of 1 in the CUST_NO column should be deleted by the statement. The BEFORE UPDATE trigger created in the previous step will also delete all ORDER13 tables with a value of 1 in the CUST_NO column.

Although the message returned in SQL*Plus shows one row deleted, it does not represent the records deleted by the BEFORE DELETE trigger. The next step queries the ORDER13 table to show whether the records were deleted.

4. Run the CHP13_19.SQL file in SQL*Plus. The query contained in the file returns columns from the ORDER13 table. Figure 13.19 displays the records returned by the query.

The absence of a WHERE clause allows all records from the ORDER13 table to be returned by the query.

The records returned by the query do not include any records in which the CUST_NO column is 1. The BEFORE DELETE trigger removed the records when the DELETE statement was executed in Step 3.

How It Works

When referential integrity is not enforced through constraints, cascading deletes can be performed using BEFORE DELETE FOR EACH ROW triggers. Step 1 creates two sample tables. The CUST13 table contains the customer details and the ORDER13 table contains orders placed by the customers. When a customer is deleted from the database, it is important to remove all orders placed by that customer. Step 2 creates a BEFORE DELETE trigger on the CUST13 table, which deletes orders from the ORDER13 table for the customer being removed. Step 3 demonstrates the execution of the trigger by deleting a record from the CUST13 table. When the record is deleted, the BEFORE DELETE trigger removes all orders for that particular customer from the ORDER13_16 table. Step 4 queries the ORDER13 table to show that the order record has been removed.

Comments

Keep in mind that this technique does not work if referential integrity is enforced through constraints. If you are using constraints, you can have the constraints perform deletes automatically. Beware also of the effects of unintentional cascading of updates and deletes.