Chapter Eight. Complex Rule Enforcement

The following scenario is based on the data in the STUDENT_VEHICLES table and the PARKING_TICKETS table. Refer to Chapter 4, Section 4.4, for the sample data.

A business rule can sometimes have a recursive nature. A general scenario is when an UPDATE statement executes and an update trigger modifies other rows in that same table. Deletes can also be recursive. Certainly, the foreign key delete cascade is one method of deleting dependent data. Other times, the business rule is complex and a delete trigger must procedurally determine if additional deletes are required.

The following scenario demonstrates a recursive delete. The delete row trigger may delete additional rows. It may not. It depends on the data. The business rule is:

When a student pays a parking ticket, all other tickets for the same car and for the same amount are deleted as well, provided the sum of the tickets to be deleted does not exceed $10.00.

The rationale is that tickets are sometimes duplicated . The assumption is that multiple tickets for the same amount and the same car are accidental duplicates. If the sum of the tickets is excessive, then this is probably not the case. Hence the limit of $10.00.

A student can have three tickets, each $3.00. All are deleted when one ticket is paid. The data from the Parking Tickets table in Chapter 4 shows that there is one case of this. The vehicle with New York tag number MH 8709 has a duplicate parking violation. Each ticket is $5.00. According to this rule, one paid ticket deletes both parking tickets from the table.

The task is to develop a trigger that takes the specific action to delete additional records within the same table. A ROW trigger cannot read or perform any DML operation on the table being modified. The reason is the table is being updated. Oracle guarantees consistent reads by queries read from rollback segments. The SELECT or any DML operation within the context of a row trigger generates an Oracle ORA-0491 Table Mutating error.

The approach in these situations is to capture row values that are needed to enforce the business rule and make them available to an AFTER statement trigger. Chapter 7 ended with an example similar to this. The following is a first step at that process. The package below is designed to capture the STATE, TAG_NO, and AMOUNT from the ROW trigger. This data is stored in a global temporary table. The following is the definition of the global temporary table. Upon a commit or rollback, data is cleared from the table.

 CREATE GLOBAL TEMPORARY TABLE parking_tickets_g (state  VARCHAR2(2),  tag_no VARCHAR2(10),  amount NUMBER) ON COMMIT DELETE ROWS; 

The next step is a package that interfaces with the row and statement trigger, shown in Figure 8-1. The row trigger will pass values in a procedure call. Those values are inserted into the global temporary table. The statement level trigger calls a procedure that reads the row level data from the temporary table and enforces the business rule.

Figure 8-1. Parking Ticket Deletes Architecture.

The following is the specification and body of the trigger interface package. This is called the PARKING_TICKETS_CONS package. This code accepts row data from the row trigger. For statement level processing it simply prints the data read from the temporary table. This will be revised shortly to include the deletes.

 CREATE OR REPLACE PACKAGE parking_tickets_cons IS     PROCEDURE load_temp_table         (v_state parking_tickets.state%TYPE,          v_tag_no parking_tickets.tag_no%TYPE,          v_amount parking_tickets.amount%TYPE);     PROCEDURE remove_duplicates; END parking_tickets_cons; 

The package body is:

 CREATE OR REPLACE PACKAGE BODY parking_tickets_cons IS     PROCEDURE load_temp_table         (v_state parking_tickets.state%TYPE,          v_tag_no parking_tickets.tag_no%TYPE,          v_amount parking_tickets.amount%TYPE)     IS     BEGIN         INSERT INTO parking_tickets_g VALUES             (v_state, v_tag_no, v_amount);     END load_temp_table;     PROCEDURE remove_duplicates IS     BEGIN         FOR rec in(SELECT * FROM parking_tickets_g) LOOP             dbms_output.put_line                 ('REC:'rec.state                  ' 'rec.tag_no' 'rec.amount);         END LOOP;     END remove_duplicates; END parking_tickets_cons; 

The row trigger will call the aforementioned procedure LOAD_TEMP_TABLE. The statement level trigger will call REMOVE_DUPLICATES, which for now prints the rows selected from the temporary table.

The row and statement level triggers are:

 CREATE OR REPLACE TRIGGER parking_tickets_adr AFTER DELETE ON parking_tickets FOR EACH ROW BEGIN     parking_tickets_cons.load_temp_table         (:old.state, :old.tag_no, :old.amount); END; CREATE OR REPLACE TRIGGER parking_tickets_ads AFTER DELETE ON parking_tickets BEGIN     parking_tickets_cons.remove_duplicates; END; 

If we execute the following SQL statement, the vehicle information is printed.

  SQL>  DELETE FROM parking_tickets  2  WHERE ticket_no = ='P_02';  REC:NY MH 8709 5   1 row deleted.  

The final step is to revise the logic in REMOVE_DUPLICATES. This procedure must query the PARKING_STUDENTS table to determine if there are other rows for the same vehicle and same ticket amount.

One row is already deleted. To determine if the sum of tickets is within the $10.00 range, the ticket amount (of the deleted row) must be added to the SUM(amount) left in the table. The revised REMOVE_DUPLICATES is the following:

 PROCEDURE remove_duplicates IS     ct NUMBER; BEGIN     FOR rec in (SELECT * FROM parking_tickets_g) LOOP         SELECT SUM(amount) INTO ct         FROM   parking_tickets         WHERE  state = rec.state         AND    tag_no = rec.tag_no         AND    amount = rec.amount;         ct := ct + rec.amount;         IF ct <= 10 THEN             DELETE FROM parking_tickets             WHERE state=rec.state AND tag_no=rec.tag_no;         END IF;     END LOOP; END remove_duplicates; 

Now the duplicates are removed. The following illustrates the single delete using the ticket number. The after statement trigger enforces an internal delete cascade based upon business logic.

 SQL> SELECT * FROM parking_tickets; TICKET_NO      AMOUNT ST TAG_NO ---------- ---------- -- ---------- P_01                5 CA CD 2348 P_02                5 NY MH 8709 P_03                5 NY MH 8709 P_04                5 NY JR 9837 SQL> DELETE FROM parking_tickets WHERE ticket_no='P_02'; SQL> SELECT * FROM parking_tickets; TICKET_NO      AMOUNT ST TAG_NO ---------- ---------- -- ---------- P_01                5 CA CD 2348 P_04                5 NY JR 9837 

The after statement trigger includes a DELETE statement on the table from which the original delete was applied.