common trigger pattern - add a set of rows

[Robert Dyas]

Below is an example of a common trigger pattern - inserting a set of child rows when a single parent row is added.

This is often done in systems where there is a "template" of information to be captured, but the "template" of what should be captured varies by the type of item.

The trigger below is an AFTER INSERT trigger on a table that holds Inspection records. An inspection if of a certain type of part has multiple manufacturing Operations and each operation in turn has a series of Inspection_Items that need to be performed.

BEGIN

DECLARE v_item_id BIGINT;

DECLARE v_no_more_rows BOOLEAN DEFAULT FALSE;

DECLARE main_cursor CURSOR FOR 

SELECT Items.Item_ID 

FROM Items

WHERE Items.Operation_ID = NEW.Operation_ID ;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_no_more_rows = TRUE;

OPEN main_cursor;

read_loop: LOOP

FETCH main_cursor INTO v_item_id;

IF (v_no_more_rows = TRUE) THEN

LEAVE read_loop;

END IF;

INSERT INTO Inspection_Items (Inspection_Item_ID, Inspection_ID, Item_ID) 

VALUE (parasql_next_counter_value('Inspection_Items','Inspection_Item_ID'),  

   NEW.Inspection_ID, v_item_id);

END LOOP;

CLOSE main_cursor;

END