Oracle APEX动态审批工作流：合并触发器实现多部门分级审批

Absolutely! You can absolutely consolidate your triggers and build a dynamic approval workflow that automatically adapts to each department's APPROVAL_LEVEL from the P_IT_DEPARTMENTS table. Here's a step-by-step solution to eliminate hardcoded triggers and create a scalable system:

Step 1: Enhance the Issues Table for State Tracking #

First, add a field to P_IT_ISSUES to track the current approval stage—this is critical for knowing which level to advance to next:

ALTER TABLE P_IT_ISSUES ADD (CURRENT_APPROVAL_LEVEL NUMBER DEFAULT 0);

Step 2: Create a Reusable Approval Handling Procedure #

Wrap the core approval logic in a stored procedure. This centralizes your code for easier maintenance and adaptability:

CREATE OR REPLACE PROCEDURE PROC_HANDLE_DYNAMIC_APPROVAL(
    p_issue_id IN P_IT_ISSUES.ISSUE_ID%TYPE,
    p_approve_this IN P_IT_ISSUES.APPROVE_THIS%TYPE
) AS
    v_current_level NUMBER;
    v_max_level NUMBER;
    v_next_level NUMBER;
    v_email VARCHAR2(255);
    v_dept_name VARCHAR2(255);
    v_issue_summary VARCHAR2(255);
    v_status VARCHAR2(30);
    v_priority VARCHAR2(30);
    v_related_dept_id NUMBER;
BEGIN
    -- Pull core issue details
    SELECT CURRENT_APPROVAL_LEVEL, RELATED_DEPT_ID, ISSUE_SUMMARY, STATUS, PRIORITY
    INTO v_current_level, v_related_dept_id, v_issue_summary, v_status, v_priority
    FROM P_IT_ISSUES
    WHERE ISSUE_ID = p_issue_id;

    -- Get the department's max required approval level
    SELECT APPROVAL_LEVEL
    INTO v_max_level
    FROM P_IT_DEPARTMENTS
    WHERE DEPT_ID = v_related_dept_id;

    -- Process approval if the approver triggered it
    IF p_approve_this = 'Y' THEN
        v_next_level := v_current_level + 1;
        
        -- Update the issue's current approval stage
        UPDATE P_IT_ISSUES
        SET CURRENT_APPROVAL_LEVEL = v_next_level,
            APPROVE_THIS = 'N' -- Reset the approval trigger flag
        WHERE ISSUE_ID = p_issue_id;

        -- Check if there's another approval level needed
        IF v_next_level <= v_max_level THEN
            -- Fetch the next approver's details
            SELECT p.PERSON_EMAIL, d.DEPT_NAME
            INTO v_email, v_dept_name
            FROM P_IT_PEOPLE p
            JOIN P_IT_DEPARTMENTS d ON p.ASSIGNED_DEPT = d.DEPT_ID
            WHERE d.DEPT_ID = v_related_dept_id
              AND p.APPROVER = 'Approver ' || v_next_level;

            -- Send the dynamic approval email
            APEX_MAIL.SEND(
                p_to => v_email,
                p_from => 'it-support@yourcompany.com', -- Use a consistent official sender
                p_body => 'You have a new level ' || v_next_level || ' approval request:' || CHR(10) ||
                          '------------------------' || CHR(10) ||
                          'Department: ' || v_dept_name || CHR(10) ||
                          'Issue Summary: ' || v_issue_summary || CHR(10) ||
                          'Current Status: ' || v_status || CHR(10) ||
                          'Priority: ' || NVL(v_priority, '-'),
                p_subj => 'Action Required: Level ' || v_next_level || ' Approval for IT Issue'
            );
        ELSE
            -- All approval levels completed: mark issue as fully approved
            UPDATE P_IT_ISSUES
            SET APPROVED = 1,
                STATUS = 'Fully Approved' -- Optional: update status for visibility
            WHERE ISSUE_ID = p_issue_id;
        END IF;
    END IF;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        -- Log missing approver errors instead of failing silently
        INSERT INTO P_IT_APPROVAL_ERRORS (ISSUE_ID, ERROR_MESSAGE, ERROR_TIMESTAMP)
        VALUES (p_issue_id, 'No approver found for level ' || v_next_level || ' in department ' || v_related_dept_id, SYSDATE);
    WHEN OTHERS THEN
        RAISE; -- Re-throw unexpected errors for debugging
END;
/

Step 3: Replace Multiple Triggers with One Unified Trigger #

Now create a single trigger that calls the procedure for both insert (initial approval) and update (approval progression):

CREATE OR REPLACE EDITIONABLE TRIGGER P_IT_ISSUES_AIU_DYNAMIC_APPROVAL
AFTER INSERT OR UPDATE OF APPROVE_THIS ON P_IT_ISSUES
FOR EACH ROW
FOLLOWS P_IT_ISSUES_AIU_EMAIL
DECLARE
BEGIN
    -- On issue creation: trigger first-level approval
    IF INSERTING THEN
        PROC_HANDLE_DYNAMIC_APPROVAL(:NEW.ISSUE_ID, 'Y');
    ELSIF UPDATING AND :NEW.APPROVE_THIS = 'Y' AND :OLD.APPROVE_THIS != 'Y' THEN
        -- Only trigger when the approval flag is switched to 'Y'
        PROC_HANDLE_DYNAMIC_APPROVAL(:NEW.ISSUE_ID, :NEW.APPROVE_THIS);
    END IF;
END;
/

Key Benefits of This Approach #

• Dynamic Scalability: Automatically adapts to any number of approval levels (2, 3, or more) defined in P_IT_DEPARTMENTS—no new triggers needed for new levels.
• Maintainability: All approval logic lives in one procedure, so changes to email content, approval rules, or error handling only require updating one piece of code.
• Clear State Tracking: The CURRENT_APPROVAL_LEVEL field makes it easy to query where an issue is in the approval pipeline.
• Error Resilience: Includes basic error handling for missing approvers to prevent trigger failures and log issues for admins to resolve.

Quick Notes for Production Use #

• Ensure P_IT_PEOPLE has valid Approver 1 through Approver n entries for each department (you can add a check constraint or frontend validation to enforce this).
• Consider adding an approval history field (e.g., APPROVAL_HISTORY CLOB) to P_IT_ISSUES to log who approved each level and when, for audit purposes.
• Test with different department approval levels to confirm the workflow advances correctly and stops when all levels are completed.

内容的提问来源于stack exchange，提问作者Velocity