Oracle中如何实现删除或特定列更新时触发数据复制触发器？

Your original trigger fires on any update to tableA, which is why it's behaving broader than intended. We can fix this by adding a conditional filter directly in the trigger definition to restrict execution to only your desired scenarios.

Here's the revised trigger code:

create or replace trigger my_trigger
before delete or update on tableA
for each row
when (deleting or (updating('STATUS') and :NEW.status = 'C'))
begin
    insert into tableB(rollno, name) values(:OLD.rollno, :OLD.name);
end;
/

Let's break down the key changes: #

• The WHEN clause acts as a gatekeeper: it only allows the trigger body to run if either:

  1. A delete operation is occurring (deleting predicate), OR
  2. The STATUS column is being updated (updating('STATUS') predicate) and the new value of STATUS is exactly 'C' (:NEW.status = 'C').

• Specifying column names in the INSERT statement (instead of relying on position) is a good practice—it makes your code more resilient to future changes in tableB's structure.

Optional extra check (if needed): #

If you want to avoid inserting duplicate records into tableB when someone updates STATUS to 'C' again (when it was already 'C'), you can add an extra condition to ensure the status actually changed:

when (deleting or (updating('STATUS') and :OLD.status != 'C' and :NEW.status = 'C'))

This single trigger will now handle both your required scenarios without needing to split into separate triggers.

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