如何编写脚本检查SQL触发器中update_delete表name字段的插入值

Got it, let's break down how to solve this problem effectively. Here's a practical approach using Python (great for text processing and easy to adapt) along with clear steps and code examples:

Core Approach #

The goal is to:

• Split your SQL file into individual trigger blocks
• For each trigger, locate the INSERT INTO update_delete statement
• Verify if the name column meets your requirement:
  1. Either it's inserted with :old.name or null, OR
  2. Flag it if the column is missing entirely (since it doesn't adhere to inserting one of those two values)

Step-by-Step Implementation #

1. Parse Triggers from the SQL File #

First, we need to split the input file into separate triggers. Most Oracle-style triggers end with a / on a new line—if your triggers use END; instead, adjust the delimiter in the code below.

2. Extract & Validate Insert Statements #

For each trigger, we'll use regex to find the insert statements targeting update_delete, then map columns to their values to check the name field.

Example Python Script #

This script will scan your SQL file, flag triggers that don't meet the name column requirement, and print clear feedback:

import re
import sys

def scan_triggers_for_valid_name_insert(sql_file_path):
    # Read the entire SQL file
    with open(sql_file_path, 'r') as file:
        sql_content = file.read()
    
    # Split content into individual triggers (adjust delimiter if using END; instead of /)
    triggers = re.split(r'\n/\n', sql_content)
    
    # Iterate through each trigger
    for trigger_num, trigger in enumerate(triggers, 1):
        if not trigger.strip():
            continue  # Skip empty blocks
        
        # Find all INSERT statements targeting update_delete (handles multi-line)
        insert_matches = re.finditer(
            r'INSERT\s+INTO\s+update_delete\s*\((.*?)\)\s*VALUES\s*\((.*?)\);',
            trigger,
            re.IGNORECASE | re.DOTALL
        )
        
        for insert in insert_matches:
            # Extract columns and values, clean up whitespace
            columns = [col.strip().lower() for col in insert.group(1).split(',')]
            values = [val.strip().lower() for val in insert.group(2).split(',')]
            
            # Map each column to its corresponding value
            column_value_map = dict(zip(columns, values))
            
            # Check the name column
            if 'name' in column_value_map:
                inserted_value = column_value_map['name']
                if inserted_value not in (':old.name', 'null'):
                    print(f"⚠️ Trigger {trigger_num}: Invalid 'name' value - found '{inserted_value}', expected ':old.name' or 'null'")
            else:
                print(f"⚠️ Trigger {trigger_num}: 'name' column is missing from the INSERT into update_delete")

if __name__ == "__main__":
    if len(sys.argv) != 2:
        print("Usage: python trigger_scanner.py <path_to_your_sql_file.sql>")
        sys.exit(1)
    scan_triggers_for_valid_name_insert(sys.argv[1])

Key Adjustments for Your Use Case #

• Trigger Delimiter: If your triggers end with END; instead of /, replace the split line with:

  triggers = re.split(r'END;\s*', sql_content)
  

• Case Sensitivity: The script uses re.IGNORECASE to handle variations like INSERT INTO vs insert into—remove this if your SQL is strictly case-sensitive.
• Multi-line Inserts: The re.DOTALL flag ensures the regex matches inserts that span multiple lines (common in formatted SQL).

For More Robust Parsing (Optional) #

If your SQL has complex formatting (like nested parentheses or comments inside inserts), regex might not be enough. For production use, consider using the sqlparse library (a proper SQL parser):

import sqlparse

def scan_with_sqlparse(sql_file_path):
    with open(sql_file_path, 'r') as file:
        sql_content = file.read()
    
    parsed_statements = sqlparse.parse(sql_content)
    for stmt in parsed_statements:
        if stmt.get_type() == 'CREATE TRIGGER':
            # Traverse the parsed SQL tree to find INSERT into update_delete
            for token in stmt.flatten():
                if isinstance(token, sqlparse.sql.Identifier) and token.value.lower() == 'update_delete':
                    insert_stmt = token.parent.parent
                    # Extract columns and values from the parsed insert statement
                    # This requires more detailed tree traversal, but is more reliable
                    pass

This approach avoids regex limitations but has a steeper learning curve.

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