14.8.2.3.3 Defaulting and Validating Pre/Post-Save

Apply defaults before saving, then validate saved parent and child rows before commit.

The BEFORE_SAVE and AFTER_SAVE procedures contain the defaulting and validation checks required, using procedures in the ACTION_ITEM_COMMON package. The steps performed in BEFORE_SAVE include:

• Ensuring an insert does not supply a value for the ID primary key
• Defaulting a team member's role to MEMBER if not supplied
• Validating the value of role is only either LEAD or MEMBER.

-- in package action_items_api
procedure before_save(
    p_action_item  in out action_items%rowtype,
    p_action_item_team_members in out t_action_item_team_members,
    p_operation    in     t_operation)       
is
begin
    if p_operation = c_operation_insert then
        -- Providing an ID with insert not allowed
        app_common.report_if_error(
            action_items_common.ensure_null_id_for_new_action(
               p_action_item.id));
    end if;
    if p_operation in (c_operation_insert,c_operation_update) then
        -- Default role to member if not supplied
        for j in 1..p_action_item_team_members.count loop
            if p_action_item_team_members(j).role is null then
                p_action_item_team_members(j).role := 'MEMBER';
            else
                -- Check role value is legal
                app_common.report_if_error(
                    action_items_common.is_valid_role(
                       p_action_item_team_members(j).role));
            end if;
        end loop;
    end if;
end before_save;

The checks enforcing conditions about the action item team membership go in the AFTER_SAVE so they can query the parent and child data after it's saved, but before it gets committed. The AFTER_SAVE validations include validating that an action team:

• Contains no duplicate team members
• Has exactly one lead.

-- in package action_items_api
procedure after_save(
    p_action_item              in action_items%rowtype,
    p_action_item_team_members in t_action_item_team_members,
    p_operation                in t_operation)       
is
begin
    if p_operation in (c_operation_insert,c_operation_update) then    
        app_common.report_if_error(
            action_items_common.no_duplicate_team_members(p_action_item.id));
        app_common.report_if_error(
            action_items_common.one_lead_per_action_team(p_action_item.id));
    end if;
end after_save;