Figure 18-2. Using a collection as a work list to bypass mutating trigger errors

Kay can implement the following algorithm to subvert any chances of mutating:

1. Initialize collections in a PL/SQL package when the statement begins.

2. Store record identifiers in these collections as each row is processed.

3. Process each entry in the collections after the statement ends.

First a PL/SQL package is required. Here is the start of one that will help Kay out:

/* File on web: mutation_zone.sql */CREATE OR REPLACE PACKAGE give_away_money AS PROCEDURE init_tables; END give_away_money; CREATE OR REPLACE PACKAGE BODY give_away_money AS -- structure to hold account numbers TYPE v_account_table_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; v_account_table v_account_table_type; /*----------------------------------------*/ PROCEDURE init_tables IS /*----------------------------------------*/ BEGIN -- Initialize the account list to empty v_account_table.DELETE; END init_tables; END give_away_money;

This simple beginning takes care of cleaning out the collection to store account numbers as they are inserted. Next we have to hook up the init_tables procedure with a BEFORE statement trigger:

CREATE OR REPLACE TRIGGER before_insert_statementBEFORE INSERT ON accountBEGIN /* || Initialize collections to hold accounts that || will get 100 free dollars when we are done! */ give_away_money.init_tables;END;

Now whenever an INSERT statement is executed against the account table, the collection will be emptied.

The next step is to capture the account numbers as they are processed. Here's a possible procedure to add to the give_away_money package:

PROCEDURE add_account_to_list ( p_account NUMBER ) ISBEGIN v_account_table(NVL(v_account_table.LAST,0) + 1) := p_account;END add_account_to_list;

It attaches to an AFTER INSERT row trigger like this:

CREATE OR REPLACE TRIGGER after_insert_rowAFTER INSERT ON accountFOR EACH ROWBEGIN /* || Add the new account to the list of those in line for || 100 dollars. */ give_away_money.add_account_to_list(:NEW.account_id);END;

Last but not least, a procedure in the package finally gives the money away:

PROCEDURE give_it_away_now IS-- Create $100 deposits for accounts that have been created v_element PLS_INTEGER;BEGIN v_element := v_account_table.FIRST; LOOP EXIT WHEN v_element IS NULL; INSERT INTO account_transaction (transaction_id, account_id, transaction_type, transaction_amount, comments) VALUES(account_transaction_seq.nextval, v_account_table(v_element), 'DEP', 100, 'Free Money!'); v_element := v_account_table.NEXT(v_element); END LOOP;END give_it_away_now;

This procedure attaches to an AFTER INSERT statement trigger as such:

CREATE OR REPLACE TRIGGER after_insert_statementAFTER INSERT ON accountBEGIN -- At long last we can give away the money! give_away_money.give_it_away_now;END;

Now after dropping Kay's original trigger and replacing it with the new package and triggers, testing reveals the following behavior:

SQL> INSERT INTO account (account_id,account_owner) 2 VALUES (1,'Test'); SQL> SELECT * FROM account_transaction 2 WHERE account_id = 1; TRANSACTION_ID ACCOUNT_ID TRA TRANSACTION_AMOUNT COMMENTS-------------- ---------- --- ------------------ ------------------------------ 1 1 DEP 100 Free Money!SQL> BEGIN 2 FOR counter IN 20..25 LOOP 3 INSERT INTO account 4 (account_id,account_owner) 5 VALUES(counter,'Test'); 6 END LOOP; 7 END; SQL> SELECT * 2 FROM account_transaction 3 WHERE account_id BETWEEN 20 AND 25 TRANSACTION_ID ACCOUNT_ID TRA TRANSACTION_AMOUNT COMMENTS-------------- ---------- --- ------------------ ------------------------------ 2 20 DEP 100 Free Money! 3 21 DEP 100 Free Money! 4 22 DEP 100 Free Money! 5 23 DEP 100 Free Money! 6 24 DEP 100 Free Money! 7 25 DEP 100 Free Money! 6 rows selected.

Thanks to the powerful interaction of statement-level triggers, row-level triggers and PL/SQL packages, Kay is free to enjoy the rest of her weekend without worrying about any mutation problems.