如何在SQLite3与MS SQL中记录DELETE/UPDATE原始查询语句

Alright, let's work through this problem together. You need to log the raw DELETE and UPDATE queries for your Kittens table into kittenLog, and you're stuck on two key points: how to get the raw query in SQLite3, and how to correctly use EVENTDATA() in MS SQL. Let's break this down for both databases step by step:

Microsoft SQL Server Implementation #

First, let's tackle MS SQL, which has native support for capturing event data including raw query strings.

Understanding EVENTDATA() #

EVENTDATA() returns an XML document packed with details about the trigger-firing event. For DML triggers (DELETE/UPDATE), this XML includes a <TSQLCommand> node that holds the exact raw query text. Here's a sample of what that XML looks like:

<EVENT_INSTANCE>
  <EventType>DELETE</EventType>
  <TSQLCommand>
    <CommandText>DELETE from Kittens WHERE kittenID=1</CommandText>
  </TSQLCommand>
</EVENT_INSTANCE>

We use XQuery to extract the CommandText value from this XML structure.

Complete Trigger Code #

This trigger captures both DELETE and UPDATE operations, pulls the raw query, and logs the affected kittenID:

CREATE TRIGGER trg_Kittens_LogChanges
ON Kittens
AFTER DELETE, UPDATE
AS
BEGIN
  SET NOCOUNT ON;

  -- Use the deleted table to get affected kittenIDs (works for both DELETE and UPDATE)
  INSERT INTO kittenLog (kittenID, query)
  SELECT 
    d.kittenID,
    EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'NVARCHAR(MAX)')
  FROM deleted d;
END;

Key Notes: #

• EVENTDATA() only works within the scope of the trigger execution—it won't return valid data outside that context.
• This assumes kittenID (your primary key) isn't modified during UPDATE operations (which is standard for primary keys). If you do allow updating kittenID, swap deleted.kittenID for inserted.kittenID.
• NVARCHAR(MAX) ensures we capture even long, complex query strings.

SQLite3 Implementation #

SQLite has a critical limitation here: there's no built-in way for database-level triggers to access the raw SQL query that fired them. However, we have two practical workarounds depending on your needs:

Option 1: Log Operation Context (Without Raw Query) #

If you can accept logging the operation type and affected kittenID instead of the full raw query, here's a standard trigger setup:

-- Trigger for DELETE operations
CREATE TRIGGER trg_Kittens_DeleteLog
AFTER DELETE ON Kittens
BEGIN
  INSERT INTO kittenLog (kittenID, query)
  VALUES (OLD.kittenID, 'DELETE operation on Kittens (kittenID=' || OLD.kittenID || ')');
END;

-- Trigger for UPDATE operations
CREATE TRIGGER trg_Kittens_UpdateLog
AFTER UPDATE ON Kittens
BEGIN
  INSERT INTO kittenLog (kittenID, query)
  VALUES (OLD.kittenID, 'UPDATE operation on Kittens (kittenID=' || OLD.kittenID || ')');
END;

This gives you clear context about what happened and which kitten was affected, even without the exact query string.

Option 2: Capture Raw Queries via SQLite Trace API #

If you absolutely need the full raw query, you'll need to use SQLite's trace API (accessible via programming languages like Python, C#, etc.) instead of database-level triggers. Here's a simplified example using Python's sqlite3 module:

import sqlite3

def trace_callback(query):
  # Skip INSERT queries as requested
  stripped_query = query.strip().upper()
  if stripped_query.startswith(('DELETE', 'UPDATE')) and 'KITTENS' in stripped_query:
    # Extract kittenID (basic parsing—adjust for complex queries as needed)
    kitten_id = None
    if 'KITTENID=' in stripped_query:
      kitten_id = query.split('kittenID=')[1].split()[0]
    # Log the query to kittenLog
    conn.execute("INSERT INTO kittenLog (kittenID, query) VALUES (?, ?)", (kitten_id, query))
    conn.commit()

# Connect to your SQLite database
conn = sqlite3.connect('your_database.db')
# Enable the trace callback to intercept queries
conn.set_trace_callback(trace_callback)

# Example operation that will be logged
conn.execute("DELETE from Kittens WHERE kittenID=1")

This approach intercepts all SQL queries, filters for DELETE/UPDATE operations on Kittens, parses out the affected kittenID, and logs the full raw query.

Key Notes for SQLite: #

• Database-level triggers can't access raw queries—this is a hard limitation of SQLite's trigger system.
• The query parsing in the trace callback is basic. For complex queries (e.g., multiple conditions, subqueries), you'd need a more robust SQL parsing library to extract kittenID accurately.

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