基于条件的AFTER UPDATE触发器实现咨询（已完成INSERT触发器）

Got it, let's work through how to build that conditional AFTER UPDATE trigger you need. Based on your existing insert trigger, I assume you want to log updates to table1 only when specific columns change (or meet other business conditions) into your table2 log table.

Basic Conditional Trigger Example #

Here’s a trigger that only logs an update when either the Person1 or Item column is modified (matching the fields you track in your insert trigger):

CREATE TRIGGER update_on_tbl1 AFTER UPDATE ON table1
FOR EACH ROW
-- Trigger only runs if Person1 or Item has changed
IF OLD.Person1 != NEW.Person1 OR OLD.Item != NEW.Item THEN
    INSERT INTO table2 (Person1, Item, tag)
    VALUES (NEW.Person1, NEW.Item, 'Updated');
END IF;

Breakdown of the Logic: #

• OLD refers to the row data before the update, while NEW is the data after the update. Comparing these lets you check if specific columns were altered.
• The IF clause acts as your condition gate—only when either Person1 or Item differs between old and new records will the log entry be inserted.
• We use 'Updated' for the tag field to clearly distinguish this from your existing 'Inserted' logs.

Extending to Track Before/After Values #

If you want your log table to capture both the original and updated values (super helpful for auditing), first adjust table2 to include columns for old values (e.g., OldPerson1, OldItem). Then modify the trigger like this:

CREATE TRIGGER update_on_tbl1 AFTER UPDATE ON table1
FOR EACH ROW
IF OLD.Person1 != NEW.Person1 OR OLD.Item != NEW.Item THEN
    INSERT INTO table2 (OldPerson1, OldItem, Person1, Item, tag)
    VALUES (OLD.Person1, OLD.Item, NEW.Person1, NEW.Item, 'Updated');
END IF;

Customizing the Condition #

You can tweak the IF clause to match your exact needs:

• Log only when Item is updated to a specific value: IF NEW.Item = 'HighPriority' THEN
• Log when multiple columns change together: IF OLD.Person1 != NEW.Person1 AND OLD.Item != NEW.Item THEN
• Log when any column changes (less common, but possible): IF OLD <> NEW THEN (note: this works in some databases like MySQL for whole-row comparison)

Quick Notes #

• Make sure your table2 schema supports the columns you’re inserting into. If you add old-value fields, you’ll need to alter the table first.
• Syntax varies slightly across databases (e.g., PostgreSQL uses a WHEN clause instead of IF), but the core logic of comparing OLD and NEW values applies universally.

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