Oracle PL/SQL：能否用包/函数复用触发器实现多表备份？

Question 1: Reusing Sync Logic with a Package or Dynamic Trigger Generation #

Absolutely! You don’t need to write 20 identical triggers. There are two clean approaches to reuse the sync logic:

Option 1: Generate All Triggers Dynamically

This is the simplest approach if you have a fixed set of table pairs. Write a one-time PL/SQL block that loops through your table pairs and creates each trigger automatically using dynamic SQL.

DECLARE
    -- Define your 20 main/backup table pairs here
    TYPE table_pair IS RECORD (
        main_table   VARCHAR2(100),
        backup_table VARCHAR2(100)
    );
    TYPE table_pair_list IS TABLE OF table_pair;
    v_table_pairs table_pair_list := table_pair_list(
        ('table1', 'table2'),
        ('table3', 'table4'),
        -- Add all remaining 18 pairs here
        ('table39', 'table40')
    );
    v_cols VARCHAR2(4000);
    v_trigger_sql VARCHAR2(4000);
BEGIN
    FOR i IN v_table_pairs.FIRST .. v_table_pairs.LAST LOOP
        -- Get comma-separated columns for the main table
        SELECT LISTAGG(column_name, ', ') WITHIN GROUP (ORDER BY column_id)
        INTO v_cols
        FROM user_tab_columns
        WHERE table_name = UPPER(v_table_pairs(i).main_table);
        
        -- Build the trigger SQL
        v_trigger_sql := 'CREATE OR REPLACE TRIGGER copy_' || LOWER(v_table_pairs(i).main_table) || '_to_' || LOWER(v_table_pairs(i).backup_table) ||
                        ' AFTER INSERT ON ' || v_table_pairs(i).main_table ||
                        ' FOR EACH ROW BEGIN ' ||
                        'INSERT INTO ' || v_table_pairs(i).backup_table || ' (' || v_cols || ') VALUES (' ||
                        LISTAGG(':NEW.' || column_name, ', ') WITHIN GROUP (ORDER BY column_id) || '); END;';
        
        -- Execute to create the trigger
        EXECUTE IMMEDIATE v_trigger_sql;
        
        DBMS_OUTPUT.PUT_LINE('Trigger created for ' || v_table_pairs(i).main_table || ' -> ' || v_table_pairs(i).backup_table);
    END LOOP;
END;
/

This block will generate all 20 triggers in one go, using the data dictionary to pull column names (so you don’t have to hardcode them).

Option 2: Use a PL/SQL Package with Dynamic SQL

If you prefer a more flexible approach (e.g., adding new table pairs later without regenerating triggers), create a package that handles the sync logic, then write minimal triggers for each table pair that call this package.

First, create the package:

CREATE OR REPLACE PACKAGE table_sync_pkg AS
    PROCEDURE sync_insert(p_source_table IN VARCHAR2, p_target_table IN VARCHAR2, p_new_row IN ANYDATA);
END table_sync_pkg;
/

CREATE OR REPLACE PACKAGE BODY table_sync_pkg AS
    PROCEDURE sync_insert(p_source_table IN VARCHAR2, p_target_table IN VARCHAR2, p_new_row IN ANYDATA) IS
        v_type_name VARCHAR2(100);
        v_cols VARCHAR2(4000);
        v_bind_vars VARCHAR2(4000);
        v_sql VARCHAR2(4000);
        v_cursor SYS_REFCURSOR;
        v_val VARCHAR2(4000);
        v_cursor_id INTEGER;
    BEGIN
        -- Get column list for the source table
        SELECT LISTAGG(column_name, ', ') WITHIN GROUP (ORDER BY column_id)
        INTO v_cols
        FROM user_tab_columns
        WHERE table_name = UPPER(p_source_table);
        
        -- Build bind variable placeholders
        SELECT LISTAGG(':' || column_id, ', ') WITHIN GROUP (ORDER BY column_id)
        INTO v_bind_vars
        FROM user_tab_columns
        WHERE table_name = UPPER(p_source_table);
        
        -- Construct insert statement
        v_sql := 'INSERT INTO ' || UPPER(p_target_table) || ' (' || v_cols || ') VALUES (' || v_bind_vars || ')';
        
        -- Convert the :NEW row to a ref cursor to extract values
        p_new_row.GetRefCursor(v_cursor);
        
        -- Execute dynamic SQL with bind variables
        v_cursor_id := DBMS_SQL.OPEN_CURSOR;
        DBMS_SQL.PARSE(v_cursor_id, v_sql, DBMS_SQL.NATIVE);
        
        FOR col IN (SELECT column_id FROM user_tab_columns WHERE table_name = UPPER(p_source_table) ORDER BY column_id) LOOP
            FETCH v_cursor INTO v_val;
            DBMS_SQL.BIND_VARIABLE(v_cursor_id, ':' || col.column_id, v_val);
        END LOOP;
        
        DBMS_SQL.EXECUTE(v_cursor_id);
        DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
        CLOSE v_cursor;
    EXCEPTION
        WHEN OTHERS THEN
            RAISE; -- Handle exceptions as needed (e.g., log errors)
    END sync_insert;
END table_sync_pkg;
/

Then, create a minimal trigger for each table pair:

CREATE OR REPLACE TRIGGER copy_table1_to_table2
AFTER INSERT ON table1
FOR EACH ROW
BEGIN
    table_sync_pkg.sync_insert(
        p_source_table => 'table1',
        p_target_table => 'table2',
        p_new_row      => ANYDATA.ConvertObject(:NEW)
    );
END;
/

Question 2: Avoiding Duplicates During Bulk Loads #

Duplicates happen because triggers insert every new row from the main table into the backup, even if the row already exists. Here’s how to fix this:

1. Full Refresh Scenario

If your bulk load replaces all data in the main table (e.g., nightly full loads), truncate the backup table first to ensure it matches the main table exactly:

TRUNCATE TABLE table2; -- Replace with your backup table name

Then run your bulk load into the main table—triggers will populate the backup with fresh, duplicate-free data.

2. Incremental Load Scenario

If you’re adding new rows or updating existing ones, replace the INSERT in your trigger with a MERGE statement. This will either insert new rows or update existing ones based on a unique key (like the primary key):

CREATE OR REPLACE TRIGGER copy_table1_to_table2
AFTER INSERT OR UPDATE ON table1
FOR EACH ROW
BEGIN
    MERGE INTO table2 t2
    USING (SELECT :NEW.col1, :NEW.col2, ..., :NEW.pk_column FROM dual) t1
    ON (t2.pk_column = t1.pk_column) -- Replace with your primary key column
    WHEN MATCHED THEN
        UPDATE SET t2.col1 = t1.col1, t2.col2 = t1.col2, ..., t2.coln = t1.coln
    WHEN NOT MATCHED THEN
        INSERT (col1, col2, ..., coln) VALUES (t1.col1, t1.col2, ..., t1.coln);
END;
/

This ensures the backup table stays in sync with both inserts and updates, eliminating duplicates.

Performance Note

For very large bulk loads, triggers can slow down operations. If this is an issue, disable triggers during the load, sync the backup table manually with INSERT ... SELECT or MERGE, then re-enable triggers. Just make sure no other writes happen to the main table during this window to avoid data inconsistencies.

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